VBA to consolidate multiple worksheets into one

hi all gurus,

Needing a vba code that consolidates multiple worksheets into one single sheet. key dot points as below:

1. only selected sheets to be consolidated (in the attached file, only sheets with numbered names to be consolidated)
2. No. of columns in each numbered sheet remains the same, but rows can change
3. only contents below the row headings to be consolidated.
4. data to be consolidated in the "Table" Sheet

Any help would be greatly appreciated.

Thank you.
S

Hi shrijan,

See the following code which is included in the attached file. There are 2 Yellow CommandButton Shapes on Sheet 'Table':
a. Clear the Data Area on Sheet 'Table'
b. Put Data from the Numbered Sheets into Sheet 'Table'

In ordinary code module ModConsolidateData:

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

Lewis

Hi Mike,

They are just black columns

S

Try

Please Login or Register  to view this content.