OK, i made sure the GAMES.xlsm has the updated code/macro and when I run it, it gives me the Goofy sheet message.


Here's the GAMES file, did I do something wrong?

Attached Files

GAMES.xlsm (110.1 KB, 7 views)

Oh boy, now I'm really confused. You shouldn't need to be flipping names back and forth.

Back in post #2... I asked "What's in row 1 and what's the name of the sheet?"
Post #3... "just the same data as you see in the pic and the name of the sheet is Utility ID."
Should that response have been the name of the file is Utility ID ? and the sheet is Sheet1 ?

If yes, is a few files all using Sheet1 for their 8 columns of data ?

One of the aspects of Excel that makes it so popular is the ease by which one can quickly put together something that is useful.
Here, it's being used as a mini-database. There'd be little point in setting up a full database in, say, Access. Too much hard work. In such a database, the data isn't so easily accessible, but the reports you can produce from it are super flexible.
This is because the data management side of things is separated from the display/output side.

In Excel that division of roles is rarely the case, but you can have a stab at it. In the attached, which is only a variant of NoSparks' workbook, his ScratchPad, instead of being temporary, is permanent, and serves as the data management side of things. Here you edit your database, sort it and anything else. I've called that sheet EditMe.
The original Sheet1 I've renamed PrintMe, and serves as the display/output. All you do here is tart it up for output; put borders in, highlighting, page breaks, cell colouring… what you will. All I've done is put links in this sheet to the EditMe sheet. So when alterations/sorting and so on take place on the EditMe sheet, they're immediately reflected in the PrintMe sheet.

There is a little macro to refresh the links on the PrintMe sheet links (which again is one of NoSparks' macros with a few tweaks) but you only to need to run that if you've added lots more entries on the EditMe sheet, or you've deleted/inserted entire rows on the EditMe sheet. For the most part, adding a few entries at the bottom of the EditMe sheet, then sorting that sheet, results in a ready-to-print PrintMe sheet.


The only things you might need to adjust in the macro are the 2 sheet names; I've put a comment 'adjust' on each line:Just a different approach.

Attached Files

msOfficeForums40032GAMES.xlsm (178.0 KB, 13 views)

I'm confused now NoSparks. I thought whatever name I saved a file was the sheet name. Say in my Utility ID (file), I thought that was 'Sheet1' and when I opened up ScratchPad, that was 'Sheet2".

p45cal, thanks for your input and I'll try it out as soon as I get my brain thinking right as what is called what, that may be my main problem right now trying to follow everything so far.

Can someone explain how to do Sheets, I tried looking for it and can't find anything about how to make Sheets like you guys are talking about.

@ thumb
I suggest you ignore everything from me and go with the pro.

You've done good NoSparks, it's me that is messing up.


I did however think I know how to add a sheet. I did add a sheet, named both of them accordingly and did the alt F8. It then asked me to name by disk or name. No matter which one I chose it came back with an error: X 400


Looking in the ScratchPad sheet, it had moved everything up to Willow(row 2695) and stopped where the data went from 4 columns down to 2 columns.

Sorry thumb, I'm lost as to what you've got and what you're doing or done with it.

p45cal has posted a better solution.
I'll have to leave it to him as to how it can be implement with your files.

p45cal, I ran your file and although it works, it's not exactly what I need the sort to do. I'll see if I can explain it simply. When the data gets sorted and put into the GAMES sheet, the new entry goes where it's suppose to and then every entry after that moves down one row until it gets to row 50 in which the entry that was at row 50 gets moved to the top of row 1. The last entry that is in row 50 column J/K gets moved to row 51 column A/B and everything repeats itself until row 100 and then repeats itself again every 50 rows until it gets to the end. Right now if I add an entry ASTRO CHAS 777b and sort it, it puts it before the two ASTRO CHASE entries but nothing gets moved. It should move the second ASTRO CHASE 187B up to row 1 column J/K and the entry that was at row 50 column J/K(BASEBALL 637B) should get moved to row 51, column A/B.



The last file that NoSparks posted did that so maybe you could take a look at his and see what he did to understand what I need. Other than that your way seems simpler and easier to work with especially for future files.



Something just occurred, maybe I'm doing the sort wrong in the editme sheet. What I am doing is adding an entry which is ASTRO CHAS 777B, then clicking on sort which a drop down comes up asking me how to sort it. Column A? Well, I need to have column B go along for the ride so I add a level to the sort so the final sort is by column A, then column B. Is that the way I'm suppose to be sorting?

In the file that I atached, I seem to have accidentally deleted cell A1 of the EditMe sheet; previous files show that there should be 720 in that cell. This means that any subsequent sort based on column A would put that empty cell's row to the bottom. It looks as if this was happening at the same time as your sort after entering ASTRO CHAS, so one row moved down to the bottom (meaning everything moved up a row) and your new row inserted itself above the other ASTRO entries, means that everything below that moved down a row, which results in everything below your new entry staying in the same place!

As an exercise, take that workbook afresh from the internet, reinstate the 720 in cell A1 of the EditMe sheet, sort it straightaway, then check where things appear in the PrintMe sheet.
Now add your ASTRO CHAS at the botttom of the EditMe sheet and sort it again, then once again review where things appear on the PrintMe sheet and I think you'll find it's behaving as you want it to.

As far as sorting goes, how you sort is up to you, whether you sort by column A then column B, or by column A only. What's important is that whichever method of sorting you choose, both columns A & B should be highlighted (this will be the default if you have any single cell selected in the EditMe list when you go to sort - Excel will highlight the lot). This ensures that when the sort happens, data in one cell will follow its neighbour (left or right).

I had to fix a whole column that was missing the ID numbers for the names plus I added the 720. Added Astro Chas and sorted and all seems to be OK.



Couple of questions now. If I have to edit something, say like deleting an entry, do I highlight both cells of that entry and right click it and choose Delete and when the popup window comes up and asks how I should move the cells, would I choose 'Shift Cells Up'?


Another question is can I change the name of the file when saving it if I want to like change your MSOFFICEFORUMSXXXX.XLSM to Games_Name.xlsm?


Another question, when I do edit something, do I have to do it in the EditMe sheet or does it matter?


Last question, I have another file for Utility that I have set up exactly the same way so could I just open that file up and add the Macro to it?


I'll get back to you if I run into a problem and I want to thank you so much for your help, I would have never been able to do this myself or if I could have, it would have taken me years!

That's one way. It's likely you'd need to run the macro to refresh the links on the PrintMe sheet as you'll have deleted cells which had a link to them (there's no harm in running this macro at any time). Another way is just to highlight both cells and press the delete key on the keyboard. Be aware though, that when sorting, because you'll have created an entire blank row, you'll have to be sure that ALL your data is selected before sorting (Excel will think it's two tables (or more)).



Yes, either change it manually in File Explorer (aka Windows Explorer), or use File|Save As to save it under a new name (you will still have a back-up copy under the original name).



If it's data, edit it on the EditMe sheet only - that's the point; to separate data changes from the display/print-out. If you want to change output column widths, shading and such like, then you do that sort of thing on the PrintMe sheet.



Yes, but make sure the sheet names match on the code lines with the 'adjust' comment.


I see on your original workbook you had many connections/queries: one to a file on your desktop called games1.txt, and many to a file called games2.txt. This is where you've imported data into your workbook. These queries are all over the place and would mess up your sheet if they were to be refreshed. Really, they should be deleted (along with their associated Names). If you want me to do that then attach the file here, otherwise if you were confident enough you could run a little macro, only once, to delete them:Note that it works on whichever workbook is the Active workbook, which may not necessarily be the workbook you put the code in.

Thanks for clarifying all that for me. Before you answered I went ahead and started testing things myself and it seems to have worked. I sorted by either Col-A or Col-B, Changed names, Loaded up my Utility ID file and added the Macro to it and it seems to have worked also.



As for those Games1.txt, where are they in the workbook? I know what they are, it was when I was converting text files from an old Commodore computer over to the PC but I only used the text to copy and paste...or at least I thought.

You're using Excel 2013 so I'm not sure it'll look the same as mine, but if you go to the Ribbon Data tab, in the Queries and Connections section there's a Queries and Connections button, if you click it it should show you a whole raft of connections. If you right-click on one, there should be the option to delete it. There's also a bunch of related Names; if you go to the Formulas tab of the ribbon, and choose Name Manager, you'll see those Names (you can delete them easily here). You can visualise where they are on the sheet by zooming out to 30% or less (bottom right of the window usually) and they should appear highlighted.