Copy sheets from source wkbk to another wkbk

[Diddy]

Hi everyone,

Could anyone help me to do this please.

I have a source workbook which is basically dumy data and completed analysis
sheets.

I then have multiple data workbooks (names are all different - i.e. don't
follow a pattern).

What I would like to be able to do is to copy the analysis sheets to each of
the data workbooks.

I have tried to record macros but I'm getting all tied up identifying which
sheet is activated.

Please help, I know it's going to look a lot easier when someone who knows
what they are doing has a go!

Thank you

 

[Joel]

this cod ewill copy the Analysis sheett to all workbook in a folder. Make
sure tthe sheet name Analysis is correct, and change Folder as required.

Sub copysheet()
Folder = "C:\temp\"
FName = Dir(Folder & "*.xls")
Do While FName <> ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
With OldBk
ThisWorkbook.Sheets("Analysis").Copy _
after:=.Sheets(Sheets.Count)
End With
OldBk.Close savechanges:=True
FName = Dir()
Loop
End Sub

 

[Diddy]

Hi Joel,

That's lovely

Can I just add additional Analysis sheet names to that line?

This will help more than you could ever know - so thank you!

 

[Joel]

No. You have to add a new copy line for each sheet.

ThisWorkbook.Sheets("Analysis").Copy _
after:=.Sheets(Sheets.Count)
ThisWorkbook.Sheets("Analysis 1").Copy _
after:=.Sheets(Sheets.Count)
ThisWorkbook.Sheets("Analysis 2").Copy _
after:=.Sheets(Sheets.Count)

 

[Diddy]

Thank you Joel
--
Deirdre

No. You have to add a new copy line for each sheet.

ThisWorkbook.Sheets("Analysis").Copy _
after:=.Sheets(Sheets.Count)
ThisWorkbook.Sheets("Analysis 1").Copy _
after:=.Sheets(Sheets.Count)
ThisWorkbook.Sheets("Analysis 2").Copy _
after:=.Sheets(Sheets.Count)