CODEWITHSUNDEEP
vbaexcelexcel-2010
Doug Fresh
Doug Fresh

Reputation: 79

VBA Copy from workbook and Paste to ThisWorkbook

enter image description hereI am trying to copy a template and paste it into the current workbook. But the code is erroring out. I get a run time error 1004.

Sub templateToBBU()

'OPEN TEMPLATE
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Dim sPath As String, sFile As String
Dim wb As Workbook
sPath = "C:\Users\user123\Desktop\"
sFile = sPath & "BBU_CMD_TEMPLATE.xlsx"
Set wb = Workbooks.Open(sFile)

'COPY TEMPLATE PASTE IN BBU DOC
wb.Sheets("TEMPLATE").Copy 
    After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

End Sub

Thanks all,

Upvotes: 1

Views: 1160

Answers (1)

Sukru Alatas
Sukru Alatas

Reputation: 106

I tested your code Excel 2016 and It works without problem. It seems It's a known problem for Excel 2003 - 2010 and there is a workaround. Save your template file as a Excel Template (xltx) and try as follow:

Sub templateToBBU()

'OPEN TEMPLATE
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Dim sPath As String, sFile As String

sPath = "C:\Users\User123\Desktop\"
sFile = sPath & "Book1.xltx"

ThisWorkbook.Sheets.Add Type:=sFile
End Sub

Upvotes: 1

Related Questions