Copy data from multiple worksheets & paste into one worksheet.

**Rodders** · 02-07-2012, 07:05 AM

Hi,

This is my first time on here so I hope someone is able to help me.
I have a workbook with multiple worksheets. I need to copy data from the first 11 columns of 16 of the worksheets & paste the values in a 'loading' worksheet. The worksheets that I need the data from have a similarity in that they are named '**** Pre Load'. Column 6 of the 11 columns is empty but I need the blank column in the loading file. When pasted into the loading file I need it so that the first sheets data will be pasted in cell A2:K10, for example, the next sheet will be from A11:K25 etc.

If anyone can help I would be extremely greatful.

Thanks,
Rod

**arlu1201** · 02-07-2012, 10:06 AM

Would you need the filename to be inserted from where the data has been copied?

**Rodders** · 02-07-2012, 10:08 AM

hi,

No that won't be necessary as this is all within one workbook.

Thanks

**arlu1201** · 02-09-2012, 11:24 AM

Use this code in a blank module of your file. Press Alt + F11. It will open a blank code window. On the left hand side, you will find Microsoft Excel Objects, right click and select Insert-> Module. Copy this code into the module window on the right.

Please Login or Register  to view this content.

Come back to your sheet. Go to Tools -> Macro-> Macros and run this macro.

**Rodders** · 02-09-2012, 11:53 AM

Thanks for the response.

I've tried it out but it stops on the line:

Worksheets(i).Rows("1:1").Copy Worksheets("Loading").Range("A1")

Woould it be easier if I attached a sample of the workbook?

Rod

**arlu1201** · 02-09-2012, 01:37 PM

Yes please attach. Since it was more of a generic question, i just framed a solution. You will need to change it to match your sheet names.
I guess, your "loading" sheet starts with "l" (small l") while the code asks for a capital L. You can try changing that and checking again.

If you still face a problem, please attach a file.

**Rodders** · 02-10-2012, 08:36 AM

S Figures slim version.xlsx

Hi Arlette,

Please find attached a siplified version of what I am working with.
This might make a bit more sense than in my explanation.

I look forward to hearing from you.

Thanks,

Rod

**arlu1201** · 02-10-2012, 09:14 AM

Your tabs - 1 preload, 2 preload, etc do not have headers. Please insert them so i know which columns need to be copied.

**Rodders** · 02-10-2012, 09:32 AM

Hi,

I not sure if I fully understand what you mean by "Headers".

The reason that the Pre Load tabs are just data is that I am using the formula below to extract some of the data from th "Area 1" tab. This formulahas to be in row one otherwise it misses the first cell.

=INDEX(Area 1!$CG$4:$CR$445,1+INT(ROW()/12.0001),ROW()-INT(ROW()/12.0001)*12)

Does this help?
If not could you send me an example of a header, please.

Thank you.

Rod

**arlu1201** · 02-10-2012, 09:44 AM

Should the data be copied one below the other?

By headers i mean the first line which contains the name for the column. If you do not have headers, can you tell me which columns need to be copied?

**Rodders** · 02-10-2012, 10:03 AM

Yes. The process should be along the lines of this:
Copy data from "Area1 Pre Load" (Columns A:F & H:K)
Paste this into the same columns in the "Loading File" tab.
Then copy the data from "Area 2 Pre Load" (Columns A:F & H:K) as above.
Paste this under that data from Area 1 in the "Loading File" tab. Then repeat for the other "Area* Pre Load" tabs.

Is this possible?

Rod

**arlu1201** · 02-10-2012, 11:16 AM

Use this code.

Please Login or Register  to view this content.

**Rodders** · 02-10-2012, 11:25 AM

Thanks for this Arlette.

Can this be easily adapted to paste special values?

Cheers,

Rod

**arlu1201** · 02-10-2012, 11:38 AM

For paste special, change the code from

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

**Rodders** · 02-10-2012, 11:54 AM

Brilliant!

This works a treat.

Thank you very much.

Rod

Copy data from multiple worksheets & paste into one worksheet.

LinkBack

Thread Tools

Rate This Thread

Display

Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Re: Copy data from multiple worksheets & paste into one worksheet.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions