VBA Copy from workbook and Paste to ThisWorkbook

Question

I 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,

What is the does the error say and on which line? See examples here on copy paste http://stackoverflow.com/a/34886033/4539709 — 0m3r, May 19 '17 at 19:23
It is erroring here: wb.Sheets("TEMPLATE").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) — Doug Fresh, May 19 '17 at 21:01
You have it as two lines it should be one or add ` _` at the end of the first to tell vba that it is a continuation. — Scott Craner, May 19 '17 at 21:03
Its one line in my code - it appears this way when I pasted into Stack Overflow — Doug Fresh, May 19 '17 at 21:36

score 1 · Accepted Answer · answered May 19 '17 at 22:22

1

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

answered May 19 '17 at 22:22

Sukru Alatas

106
3

My code works in all xlsx files, but not xls files. I need it to work in both :/ I cannot seem to get this code to work in this file type. – Doug Fresh May 19 '17 at 23:05
SOLUTION: SAVE FILE AS .XLS AND WORKS IN BOTH .XLS/.XLSX – Doug Fresh May 19 '17 at 23:08

VBA Copy from workbook and Paste to ThisWorkbook

1 Answers1