Consolidate Multiple Worksheets Into One

**DGA2008** · 03-29-2009, 02:14 PM

Hello all,

I have attempted to code a "capture" worksheet that will consolidate other worksheets into an importable template. The problem i'm having is when I run my code I beleive it is referenceing the capture page, instead of the other worksheets. I know there are a few ways to accomplish this, one being to reference the other pages through name or location. This will not work as the other sheet names and locations will change, the only constant is the page where all will be consolidated "change capture".

I beleive it is misreferencing as it points to a being 256, and b being 0... both should have different values.

I would like the capture page to consolidate downwards (begin the second paste after the last line of the previous paste).

Thank you in advance for your help!

Please Login or Register  to view this content.

**shg** · 03-29-2009, 02:23 PM

Maybe like this:

Please Login or Register  to view this content.

**DGA2008** · 03-29-2009, 03:58 PM

Thanks for your help, I'm stil getting an error in the code it seems. The error was Method 'Range' of object '_Worksheets' failed. wks.name properly referenced the sheet but the code stopped at iCol with a value of '0'.

What am i missing still? Does the sheet need to be activated for it to reference a cell and count?

Thanks again

**stanleydgromjr** · 03-29-2009, 04:19 PM

DGA2008,

It would help if you could post your workbook - scroll down and see "Manage Attachments".

I stepped thru code, and some data in sheet1, 2, 3.

After the first copy from sheet1, "nrow" had a value of 65536. This will prevent the paste for other sheets, and the error.

Have a great day,
Stan

**DGA2008** · 03-29-2009, 04:36 PM

Attached is a copy of the worksheet. What i would like to do is copy the contents from each sheet (from below the field headings to the end of the list and as wide as the fields listed) and consolidate into the "Change Capture" sheet. There should only be 30-100 rows copied per sheet at most, as the data shows...

Thank you for your help.

**stanleydgromjr** · 03-29-2009, 05:21 PM

DGA2008,

Your original workbook VBA area was Password Protected. I copied the sheets to a new workbook.

See the attached workbook "Tracking_Dist(1) - DGA2008 - SDG.xls", with a new macro "CopySheets".

Run macro "CopySheets".

Have a great day,
Stan

**DGA2008** · 03-29-2009, 05:46 PM

Works perfectly! Thank you for your help with the code!

**shg** · 03-29-2009, 05:49 PM

Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes