VBA to combine specific ranges in all worksheets

Hello:
I am creating a workbook for a client as such:

A user will duplicate a new order form (a new worksheet) via a macro, for each client order.
The new order form will have a data table which collects the entered order info into a clean data table. This is created with the duplication.
The user may be creating a number of orders per workbook, let's say 10 new worksheets.
The new worksheets will be named with a naming convention (Page No 1, Page No 2, Page No 3, etc...)

My question: How can I automate to have the data table in each worksheet append to each other, with the user having no knowledge of Power Query or Macros?
In other words, as each new worksheet is created, how can Excel put each worksheet containing "Page Num" in it's name, into a combined appended table? I can specify a range for the data table in each worksheet, for example: Y6:AG20.

I am open to using a combination of VBA and Power Query, if that's the best option.

Thanks so much!!

It would be much easier for us to help if we could see a sample workbook.
Attach one and show us what ranges you want copying, where and under what circumstances.

BSB

Hi BSB: Thanks for the reply!
Sample WB attached.
In summary, looking for a Macro to:
Combine data in Range: Y7:AG20, for each worksheet that contains "Page Num" in its name, into one large data table (Table1) in worksheet 'Combined Data'.Sample Cut List.zip
Thanks in advance.

I found one that works.

Please Login or Register  to view this content.