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: