You are here:

Excel/Copying Specific Sheet into 1 Main File with specific Sheet


Hi Sir,

Im currently trying to figure how to copy paste multiple workbook with multiple sheet into 1 workbook which have all the specific sheet in it.
For example i have 35 workbook with each having specific sheet name which i need to copy them to 1 workbook that i already have the specific sheet name intact(MAIN and FIX File).
Currently i have to open 1 workbook and copy the sheet (e.q CSKA) and went to the MAIN File and look for CSKA sheet and paste the info into that sheet. is there a way where i can do all this copy paste function easily instead of opening 1 workbook at a time and copy sheet by sheet to the MAIN File?

Tony Siew,

You would need to write a macro (vba code) to do that.  

You can turn on the macro recorder in the MAIN File and then open one of the workbooks and copy the data from one of the files to your MAIN File.  Then turn off the macro recorder.  You can then look at the recorded code.  

Before you do this, however, you should have the main file on a sheet next to the sheet you are going to paste to.  Then after you open the source workbook, you will come back to the main file and click the matching sheet.  Remember the macro recorder will record all you actions.  You don't want to record looking through the workbook.  Code can use the sheet names to go directly to the sheet - so as I say, you need to think through what you are going to do when you are recording.  Also, you do not want to be on the destination sheet because the recorder will just record it as the activesheet.  You want it to actually record selecting the appropriate sheet.  Also, after you copy the sheet's data, close the source workbook otherwise, you won't record the code to close the workbook and will end up with all 35 workbooks open once you add code for them.

So now that you have the basic code that you need, you would copy and paste it in the routine and modify the code to process your other workbooks and worksheets.  This may be tedious, but once you get it all done, you will just be able to run the macro to repeat the operation.

That is about the best I can tell you with the limited information you provided.  I will show you that

Sub CopyData()
Workbooks.Open C:\Myfolder\Myfile1.xlsx
set bk = Activeworkbook
bk.worksheets("CSKA").cells.copy thisworkbook.Worksheets("CSKA").Cells
bk.worksheets("Othersheet").cells.copy thisworkbook.worksheets("othersheet").Cells
bk.close SaveChanges:=False

Workbooks.Open C:\Myfolder\Myfile8.xlsx
set bk = Activeworkbook
bk.worksheets("CSKA8").cells.copy thisworkbook.Worksheets("CSKA8").Cells
bk.worksheets("Othersheet8").cells.copy thisworkbook.worksheets("othersheet8").Cells
bk.close SaveChanges:=False

End Sub

would represent code to copy sheets CSKA and Othersheet fromworkbook MyFile1.xlsx (Which it opens from C:\Myfolder directory) to the same named sheets in the workbook running the code ("thisworkbook" is a predefined reference to the book running the code and would be you Main File).  It then closes that and repeats this action with MyFile8.xlsx copying sheets CSKA8 and OtherSheet8 and closing that workbook.

All names are made up and just represent the form of the code you would have.   

The macro recorder will record you manual efforts so it will be a bit more verbose but that is an example of how it could be done if you knew how to write code yourself and could guide you in sorting out what you record.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.