Copy from other sheets into one sheet

Hi all,

I've got the following code, which copies data from other sheets within the same workbook. The idea is to place the data into one table, with the data from the next sheet going into the next empty cell etc etc.

Please Login or Register  to view this content.

I've gone wrong somewhere, as it is copying it all into one column (B) as opposed to copying across B, C and D.

Any ideas?

Possibly...

Please Login or Register  to view this content.

Getting a "For without next" error

I've added in a next, which gets it work but copies everything into one column - which is partly correct. Require data from columns C and D to be copied from the CPA worksheets into the Shared Contact Pivot worksheet.

I have attached a test copy of my workbook. The first 3 tabs are how they look in my actual workbook, with the yellow one showing the desired result.

As the data in any of the CPA tabs (There are 10 on my actual workbook) could be anything between 2 and 2000 rows worth of data, I need the code to be dynamic and copy from CPA 1 into the Shared Contact Pivot sheet, then copying CPA 2 underneath that and so on.

Attached test workbook

Perhaps

Please Login or Register  to view this content.

Thanks jindon, that worked perfectly! There are 10 CPA tabs, sometimes there will only be data in 2 or 3, so the others will be blank. When running this code it takes the headers from the blank CPA tabs and copies it into the Shared Contact Pivot. Is there a way to avoid this?

Try

Please Login or Register  to view this content.

Perfect! Thank you

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Hi Jindon,

I have added an extra column into Col C.

How would this now affect the code? Column B remains in the same place, but Columns C and D are now Columns D and E, so the code needs to change to affect those columns.

Many thanks,

You want to copy Col.B, Col.E & Col.E?

Please Login or Register  to view this content.

From the sheets entitled CPA 1, CPA 2 etc I want to copy:

Columns B:E from the second row down

INTO

B:E on the Shared Contact Pivot sheet into B2 initially and then CPA 2 into the next blank row and so on...

Then just change .Resize( ,3) to 4 in the first code.

Please Login or Register  to view this content.

What about to loop through all of the sheets entitled CPA?

Ooops, it should be the 2nd one...

Please Login or Register  to view this content.