Copying and dumping specific spreadsheets into a new workbook

Resolved · High Priority · Version 2010

Wed 21 Jan 2015, 13:37 Delegate Freddie said...

Freddie has attended:
Excel VBA Introduction course

Copying and dumping specific spreadsheets into a new workbook

Hi Guys!

Firstly, my level of VBA is basic to say the least!

I'm trying to copy over three spreadsheets named "LBARATEF", "LBARARIF", "LBAMKTLF" from one workbook named "Current" and paste them into a New Workbook.

I'd ideally want the new spreadsheets in the new workbook with each spreadsheet to be named each after the file that has been copied over (i.e. "LBARATEF", "LBARARIF", "LBAMKTLF")

Are you able to provide a code that would assist with the above as I am pulling my hair out over this!

Any help is hugely appreciated! :)

Thanks!

Freddie

Wed 21 Jan 2015, 16:25 Trainer Marius said...

RE: Copying and dumping specific spreadsheets into a new workboo

Hi Freddie,

Thank you for your question. The following macro seems to do the trick for me. You can just copy it and paste it into the Macro Editor code window of your workbook. (Press Alt+F11 to open it)

Sub CopySheets()
Sheets(Array("LBARATEF", "LBARARIF", "LBAMKTLF")).Select
Sheets("LBAMKTLF").Activate
Sheets(Array("LBARATEF", "LBARARIF", "LBAMKTLF")).Copy
End Sub

I hope this helps.

Kind regards
Marius Barnard
Excel Trainer

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Deleting a range of cells using the autofill handle

Firstly, select the range of cells for which you would like to clear the contents. Then drag the autofill handle to the the top left corner of the selection whilst holding down the shift key. Your selected contents should then be deleted.

View all Excel hints and tips