Pulling data from a sheet(s) by entering sheet name(s)

Hi there,

The following code works by checking all entries in column 'A' on Sheet1. When a sheet is found matching the name, the data from row 2 is copies onto Sheet1 in column C at the bottom of any existing data.
Data is copies in the order the Sheets are listed in column 'A'.
Sheet names in column 'A' must be exact as the sheet names (case sensitive).

Let us know if that works for you.

Please Login or Register  to view this content.

VB is probably the most effective way to do this...

You could do formulas but this requires presetting the data with a fixed number of sheets able to be pulled in and a max row limit...

This is limited to 3 sheets and 3 columns... you would need to change the reference that is RED below to add additional columns and add an entire BLOCK to add more sheet availability...each time you add a sheet you have to subtract the previous block... I limited this to 2000 rows from the sheets but you can add more and drag these 3 formulas down to get beyond the max (Which would be 6k right now provided each sheet had that many...



C4 =

Please Login or Register  to view this content.

D4 =

Please Login or Register  to view this content.

E4 =

Please Login or Register  to view this content.

This is great! However I am having some trouble 'tuning' it to my application.

I cannot get a "Run Macro" button like in your attachment.
The data from other sheets is not pasted in order, instead its pasted over the previous inputs

When running the macro, the data from the sheets is listed in order as they are in column 'A'.
In your original sample file you had 3 sheets, getting an output list of 9 items.
Running the macro from your original sample file, I get all 9 items.

In my sample file you should get only the 6 item values of 'Sheet2' & 'Elements'; Wheel, Clock, Nut, Door, Handle, Window listed from cell 'C4' down.
Values from 'Sheet3' (does not exist) and 'sheet4' (case from sheet name does not match) are ignored. I have done this to illustrate how it works.


For a button, insert any shape or picture. Right-click on it and select 'Assign Macro'. Select the macro you want to run...

Instead of a 'Run Macro' button, you can run the macro from the Developer tab > Macros

Hi Coni,

What exactly is not working for you?
The code does exactly what you asked for.

Please refer to the attached file.

Any text starting from cell 'A1' on each sheet will be copied, irrespective of how many rows or columns (up to the first empty row/column).
I have added a few different samples to the attached file. Just press the button and see the result.
You will get all 20 entries from the 5 sheets.

If your actual data is different from the original sample file, may upload a file (sanitized without any confidential info of course) to test on.