Copy Excel Sheet to a Closed Workbook

[trevolly]

Hi all,

I have made a workbook with a Handover sheet in it for work and I want to be able to press a command button at the end of the shift and excel copy the sheet "Handover" to a closed workbook. I have found a vba formula for this but it fails with a "Run-time error 9: Subscript out of range" error. It does open the closed workbook but doesn't paste the sheet "Handover" and then save and close the closed workbook.

The open workbook is named "Test" atm, the closed workbook I want to copy to is called "Test2". There are three tabs in the closed workbook (Called Duty Team, Daily Log Report and Closures, in that order) and id like the worksheet to paste in to the Test2 closed workbook after the Closures tab. I know the "open worksheet command" and "close and save worksheet command" work, I just cant work out the copy specific worksheet and paste to the other worksheet after all of the sheets.

The vba code Im running is....

Sub CopySheetToClosedWB()
Application.ScreenUpdating = False

Set closedBook = Workbooks.Open("C:\Users\Trevor\Desktop\test2.xlsm")
Sheets("Handover").Copy Before:=closedBook.Sheets(1)
closedBook.Close SaveChanges:=True

Application.ScreenUpdating = True
End Sub

Could anyone please help? This would complete my project for work.
Again, many thanks

 

[JoeMo]

Maybe:

Sub CopySheetToClosedWB()
Dim SourceSht As Worksheet
Set SourceSht = Sheets("Handover")
Application.ScreenUpdating = False

Set closedBook = Workbooks.Open("C:\Users\Trevor\Desktop\test2.xlsm")
SourceSht.Copy After:=closedBook.Sheets("Closures")
closedBook.Close SaveChanges:=True

Application.ScreenUpdating = True
End Sub

 

[trevolly]

@JoeMo Thank you so so much. That seems to work just right.

 

[JoeMo]

@JoeMo Thank you so so much. That seems to work just right.

You are welcome - thanks for the reply.

 

[slapshock]

Maybe:

Sub CopySheetToClosedWB()
Dim SourceSht As Worksheet
Set SourceSht = Sheets("Handover")
Application.ScreenUpdating = False

Set closedBook = Workbooks.Open("C:\Users\Trevor\Desktop\test2.xlsm")
SourceSht.Copy After:=closedBook.Sheets("Closures")
closedBook.Close SaveChanges:=True

Application.ScreenUpdating = True
End Sub

@JoeMo I have a same situation like this. Could you please change the Sheet name to variable strings? I have lot of Worksheets with different names. and need to search and copy.

 

[JoeMo]

@JoeMo I have a same situation like this. Could you please change the Sheet name to variable strings? I have lot of Worksheets with different names. and need to search and copy.

Not sure I understand what you want. You want to copy specific sheets, all to the same closed workbook ..... ?