Copy Excel Sheet to a Closed Workbook

trevolly

New Member
Joined
Aug 22, 2021
Messages
48
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Screenshot 2021-09-06 181735.jpg
    Screenshot 2021-09-06 181735.jpg
    186.3 KB · Views: 5

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,550
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Maybe:
VBA Code:
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
 
Solution

slapshock

New Member
Joined
Sep 13, 2021
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
Maybe:
VBA Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,550
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@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 ..... ?
 

Forum statistics

Threads
1,143,909
Messages
5,721,455
Members
422,363
Latest member
Bogus_Potatoes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top