Can I copy sheets from multiple excel workbooks to one workbook?

Steveire · 24-08-2006 10:52AM #1

I have several xls documents of results of a test I'm doing.
The documents are copies of an empty results workbook which are filled.
Now I have a folder full of results files which have the same structure, but different results based on certain parameters.

I want to copy "Table1" from each file into a new workbook to have a summary of results. Is that possible to do with a macro? If possible I'd like to rename them "param1" etc, where param1 is the name of the workbook it comes from.

Corkey123 · 24-08-2006 11:25AM

Steveire

Let me get this straight. You have multiple spreadsheets which are copies of another hence they are all in the same format. These speadsheets all have a particular table that you want to merge into one spreasheet.

You could simply copy and use the paste special command. This will link the copied table to the original. Or it is you have a high volumne of tables to copy and you want to automate the process with a macro.

Steveire · 25-08-2006 09:17AM

Hi.

Yes, I have multiple excel files with names like "results with parameter1", "results with parameter2" etc. Each has some sheets in it, one of which is called "output of machine1". So I have a folder full of these things. If I were to copy the sheets manually, I would open the excel file, right click on the tab at the bottom "output of machine1", and select copy. Then I would select the workbook called "Results summary for machine1", tick the box which says make a copy, and hit ok. Then I would rename the copy in the "Results summary machine1" workbook as "parameter1 results".

I'd do this for each workbook in the folder, and when finished, I'd have an excel sheet with results for machine1 for all of the parameters I ran the test with.

I'd like to have a macro automate this if possible. I tried recording a macro while doing the above, but the result didn't contain the code to paste the sheet. I think it may be impossible.

Corkey123 · 25-08-2006 02:27PM

Steveire

The macro will record the details if you select the rang of cells and copy to the final spreadsheet. Your problem is you will need to create a do loop in the code to open each sheets indepenently, copy the exact range of cell(assuming there are in exactly the sam spot) close the sheet and copy into the final spreadsheet.

All this code should be place behind a button the spreadsheet you wish to copy all to.

1. Then get the code to open the designated spreadsheet
2. I suggest you begin identifying the code via the macro.
3. Add to the code so as to paste the text
4. Close the spreadsheet

Once you have done this it is a matter of experimenting with the do loop to open and close each odf the designat spreadsheets. If they are named as you suggest i.e. output of machine1...machine 2...machine3 this should be difficult.

Below is the macro to slect a range of cell and paste to the same spreadsheet.

Sub Macro1()
'
'
'
'
Range("A3:D7").Select
Application.CommandBars("Stop Recording").Visible = False
Range("A3").Select
ActiveCell.FormulaR1C1 = "test1"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A6"), Type:=xlFillDefault
Range("A3:A6").Select
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:C3"), Type:=xlFillDefault
Range("A3:C3").Select
Range("A4").Select
Selection.AutoFill Destination:=Range("A4:C4"), Type:=xlFillDefault
Range("A4:C4").Select
Range("A5").Select
Selection.AutoFill Destination:=Range("A5:C5"), Type:=xlFillDefault
Range("A5:C5").Select
Range("A6").Select
Selection.AutoFill Destination:=Range("A6:C6"), Type:=xlFillDefault
Range("A6:C6").Select
Range("E6").Select
Application.Run "test.xls!Macro1"
Range("A3:C6").Select
Selection.Cut
Range("A13").Select
ActiveSheet.Paste
End Sub

Can I copy sheets from multiple excel workbooks to one workbook?

Comments