copy to closed workbook

[jtodd]

Hi , I would like to copy the active sheet of my workbook into a closed workbook .
Eg when the user runs the macro it will copy the active sheet , open a specified workbook and copy it into this workbook leaving the workbook open .
I have tried the folowing code but it copies the active sheet in workbook it has opened , not the active sheet from the file that is already open.
Hope this makes sense

Sub CpyShtToNewWB()
Application.ScreenUpdating = False
 
    Set closedBook = Workbooks.Open("S:\Jim Todd\Testing_Recovered\RDC_Excel_testing\7099_TNsMaster.xlsm")
     ActiveSheet.Copy Before:=closedBook.Sheets(4)
    'closedBook.Close SaveChanges:=True
 
Application.ScreenUpdating = True
End Sub

 

[sykes]

UNTESTED! Test on a COPY of your work, first.

Sub CpyShtToNewWB()
Dim cur_sht As Worksheet
Dim closedBook As Workbook

Application.ScreenUpdating = False
 
    Set cur_sht = Application.ActiveSheet
 
    Set closedBook = Workbooks.Open("S:\Jim Todd\Testing_Recovered\RDC_Excel_testing\7099_TNsMaster.xlsm")
    cur_sht.Copy Before:=closedBook.Sheets(4)
    'closedBook.Close SaveChanges:=True
 
Application.ScreenUpdating = True
End Sub

 

[jtodd]

Hi
sorry for delay ,

all working fine now , many thanks for your time

 

[sykes]

Pleasure! Thanks for the feedback.