Copy Excel Sheet to a Closed Workbook

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
92
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: 13

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,029
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
10
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
18,029
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,186,112
Messages
5,955,911
Members
438,225
Latest member
rsur

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