Copy and Paste Worksheet From Different Workbooks With a Variable

[DaveHappyNorm]

Hello all

I was hoping some could help me with the following:-

I am trying to copy a sheet from one workbook to another workbook but repeat this process over and over again.

I have an open workbook called TIF.
I want to open a different workbook called Template, copy the sheet "Current"
Paste the sheet "Current" to the workbook TIF
Close the Workbook Template.
Because I will be copying the sheet "Current" from the Template workbook into different workbooks i.e. TIF TIF1 TIF2 TIF3 etc I stored the workbook name as a variable.

The code I have is as follows:-

Dim Site_Name as string
Site_Name = TIF.xlsx

Workbooks.Open Filename:= _
"C:\Template.xlsx"
Sheets("CURRENT").Copy Before:="C:\Work" & Site_Name

Windows("Template.xlsx").Activate
ActiveWindow.Close

But this code does not work.

Can anyone help with a solution?

Thank you.

 

[JoeMo]

Untested but this should allow you to copy the template sheet to an open workbook whose name starts with TIF, provided no other workbooks with a name starting with TIF are open.

Dim wb As Workbook, Site_Name As String
'assumes you have only one workbook with a name that starts with TIF open.
For Each wb In Workbooks
    If wb.Name Like "TIF*" Then
        Site_Name = wb.Name
        Exit For
    End If
Next wb
Workbooks.Open Filename:="C:\Template.xlsx"
Sheets("CURRENT").Copy Before:=Workbooks(Site_Name).Sheets(1)   'change sheet to suit
Workbooks("Template.xlsx").Close savechanges:=False