Copy sheet from one workbook to another

CC268

Active Member
Joined
Mar 7, 2016
Messages
328
I keep getting a "Method 'Copy' of object '_Worksheet' failed. The red line of code is what is giving me trouble. Not sure why. I am sure I am missing something silly here. I am just trying to copy a sheet from one workbook to another (trying to copy it to Sheet1). I have this code as an Excel Add-in. Not sure if this is creating issues with "ThisWorkbook".

Code:
Dim CopyFromWbk As Workbook, CopyToWbk As Workbook
Dim ShToCopy As Worksheet
Dim Answer As VbMsgBoxResult


Answer = MsgBox("                  OPEN CM AS BUILT", vbOKCancel, "                  *OPEN CM AS BUILT*")
If Answer = vbCancel Then Exit Sub
Set CopyFromWbk = FileDialog_Open()
If CopyFromWbk Is Nothing Then End


Call Sheet_Selector


Set ShToCopy = CopyFromWbk.ActiveSheet
Set CopyToWbk = ThisWorkbook
[COLOR=#ff0000]ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)[/COLOR]
ActiveSheet.Name = "Sheet1"
CopyFromWbk.Close False
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
I have this code as an Excel Add-in. Not sure if this is creating issues with "ThisWorkbook".
Almost certainly. Try this
Code:
Dim CopyFromWbk As Workbook, CopyToWbk As Workbook
Dim ShToCopy As Worksheet
Dim Answer As VbMsgBoxResult

Set CopyToWbk = ActiveWorkbook
Answer = MsgBox("                  OPEN CM AS BUILT", vbOKCancel, "                  *OPEN CM AS BUILT*")
If Answer = vbCancel Then Exit Sub
Set CopyFromWbk = FileDialog_Open()
If CopyFromWbk Is Nothing Then End


Call Sheet_Selector


Set ShToCopy = CopyFromWbk.ActiveSheet
ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)
ActiveSheet.Name = "Sheet1"
CopyFromWbk.Close False
 
Upvote 0
Almost certainly. Try this
Code:
Dim CopyFromWbk As Workbook, CopyToWbk As Workbook
Dim ShToCopy As Worksheet
Dim Answer As VbMsgBoxResult

Set CopyToWbk = ActiveWorkbook
Answer = MsgBox("                  OPEN CM AS BUILT", vbOKCancel, "                  *OPEN CM AS BUILT*")
If Answer = vbCancel Then Exit Sub
Set CopyFromWbk = FileDialog_Open()
If CopyFromWbk Is Nothing Then End


Call Sheet_Selector


Set ShToCopy = CopyFromWbk.ActiveSheet
ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)
ActiveSheet.Name = "Sheet1"
CopyFromWbk.Close False

Thanks that worked - I tried using ActiveWorkbook previously but was using the Set function after opening up the workbook I was copying from.

One thing though - I would like to copy it TO Sheet1 and right now it is copying it AFTER Sheet1. How would I do that?

Thanks
 
Upvote 0
Do you want to delete the current contents of sheet1, or add the new data below any existing data?
 
Upvote 0
Do you want to delete the current contents of sheet1, or add the new data below any existing data?

Sorry for the late reply. I made the code you provided work just fine - I just added it after Sheet1, then deleted Sheet1, and then renamed it to Sheet1. Kind of a round a bout way of doing it, but it works fine.
 
Upvote 0
Glad you sorted it & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top