VBA to copy specific range

[lukestkd]

Hi all

I'm tyring to write some VBA to copy data from 10 wbs and paste it in a long list one after the other in a different wb on one sheet.
Copy ranges A2:A34, H2:H34, L2:O34
Sheet “Project 1(SPT)”
In wbs: 7aIt1, 7aIt2, 7aIt3, 7aIt4, 7aIt5, 7bIt1, 7bIt2, 7bIt3, 7bIt4, 7bIt5
AND

To wb: “2013-14 Autumn KS3 SPT”
Sheet: “Upload Data”
In Columns A:F

Directory T:\Common Area\ICT\Tracking\New Y7

Does anyone have any suggestions on how to do this?

[djdjdj]

Assuming that your workbooks are closed, I would start with something like this:

Please Login or Register  to view this content.

Also, I'm not sure what you mean "In Columns A:F", you are only copying 3 columns, not 6...

This is untested.

[lukestkd]

Hi djdjdj,
Thank you for your vba! It is correctly opening and closing the wbs when I changed the file type and sheet name, but I cannot get it to copy the ranges in the

Please Login or Register  to view this content.

to copy and paste as values in the destination sheet. How can I get it to do this?

Please Login or Register  to view this content.

Thank you again! Luke

[djdjdj]

Ah, I think I see the problem. Try changing all of the ws.Range lines from this:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

[lukestkd]

Hi djdjdj,
I have changed it to

Please Login or Register  to view this content.

This is copying the data in column A and column L, but not H or M,N,O.

It is also pasting from row 34, pasting on top of the data each time it copies it from one of the source wbs (so I end up with the data from 8bIt5).

[djdjdj]

I had set the lastrow variable based on the copy sheet, not the paste sheet! Stupid mistake

Please Login or Register  to view this content.

[lukestkd]

Hi djdjdj,
No problem Thank you for all your help!

The code is now correctly copying the columns, but is only taking the first row from each workbook.

[djdjdj]

Please Login or Register  to view this content.

[lukestkd]

Hi djdjdj,
WOW! You are a genius! Thank you so much!

Just a quick thing to tidy it up, what code would I need to delete C:G and I:K in the destination wb - so the empty columns are deleted?

I'd also like to paste a standard formula in column B - =vlookup(B2,stuid,2,0) where B2 changes based on the row, and some standard headings for Row 1.

Is the above possible? Thanks again, you've saved me a massive amount of time there!

[djdjdj]

I would setup your workbook format manually before using the code. If you delete columns with your code, your paste destinations will be off next time you run the code because the columns will shift left.

To add the formula, paste this after the Next j line:

Please Login or Register  to view this content.

[djdjdj]

You might have to do double quotes in the formula code...

Please Login or Register  to view this content.

but I'm not sure about that. Try both and see which works