How to auto-create and sort a worksheet?

I enter data about my LP records collection into a spreadsheet.
Each "box" of LPs is on a different WorkSheet: Box1 or Box2 or Box3, etc...
How can I make another WorkSheet to have all of the contents of Box1 and Box2, etc…
And automatically sort all of it in the sequence of one of the columns?
I want to enter data only into the "Box" sheets,
and have the "ByArtist" sheet automatically constantly updated.
In the sample file, I have manually created that ByArtist worksheet.
This is possible, yes?
Thanks, people.
Mark53

Hi Mark
see if this macro works
it clears the used range in sheet "ByArtist" (except the first row), and copies the contents of each sheet with a name beginning with "box" into that sheet (again except the first row), then sorts on column A
clearing the contents of your old list and creating a new one is actually quicker and easier than adding only new entries. Let me know if for some reason you'd prefer to keep the old entries in "ByArtist"

Please Login or Register  to view this content.

Wow. That's Magnificent, Nicky.
I've rarely used Macros, so it took a while to figure out how to
make it execute whenever the spreadsheet is opened. Cool.
The only problem: it copies the Formulas from the "box" sheets.
Therefore, the column D Sequence cells are wrong on the ByArtist sheet.
Can ya' tell us How to correct that?
Thanks very much.

hi
this version should paste values

Please Login or Register  to view this content.

Hi Mark53,

Alternatively, you could do it with a Pivot Table - see attached.
(no formulas - no Macros)

Regards

peterrc

That's a great improvement, Nicky.
Maybe you can also solve this problem?
If I make any change in Box1 or Box2, then save the file,
and when I open up the same file again, it gets an error:
Microsoft Visual Basic
Run-time error '1004':
The sort reference is not valid.
Make sure that it's within the data you want to sort,
and the first Sort By box isn't the same or blank.
(Continue - End - Debug - Help)

I've tried to save the attached file in that condition.
I think you will get the same error when you open it.

I'm thinking this may be a clue?: in the ByArtist sheet,
the Rows / cells that were most recently Pasted to
that ByAuthor sheet are highlighted. Is that a problem?

Thanks very much for your help, here.

Hi
auto open macros can fall over if the range being altered is not in the active sheet. Adding in a line of code to select the sheet with the sort range seems to do the trick:

Please Login or Register  to view this content.

Magnificent! Perfect! YOU are Brilliant, Nicky.

Thanks Very Much.

Waitaminute. Now, THIS is just a HYPOTHETICAL question.
I don't really Want this capability: I'm just curious as to whether or not it is possible.
The thought occurred to me:
Is it possible to have that ByAuthor sheet created and updated automatically, any time that a change is made to a Box sheet?
I know that we can assign a macro to an icon on the top row, and execute that macro whenever we want.
But can we execute that macro, to re-create the ByAuthor sheet, after any change is made in a Box sheet?

Or, might that result in a silly-CPU-intensive drain? Maybe, instead. . .
Can the macro be run whenever I Click on the ByAuthor sheet tab (to open that sheet)?
That could be cool. Is That possible?

JUST CURIOUS. How tough would that be? Your thoughts?

Thanks for your time.