Excel/Copying Specific Sheet into 1 Main File with specific Sheet
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?
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
set bk = Activeworkbook
set bk = Activeworkbook
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.