Copy worksheet from one workbook to another with VBA

[keva98]

I have 2 workbooks "Template" and "Target".

The macro will run from Target & should open "Template," copy sheet "MasterList" and paste it before the "Change Tracking" sheet in the "Target" workbook.

It's important to note that the name of the Target workbook will vary by project. So I can't specifically call it by name here.

Based on other posts, I've tried the following:

Workbooks.Open ("C:\Template.xlsx")
Workbooks("C:\Template.xlsx").Sheets("MasterList").Copy Before:=Workbooks(ThisWorkbook).Sheets("Change Tracking")


Unfortunately, I keep getting errors on this second line, no matter what I try. Any suggestions?

 

[keva98]

As an addendum....I initially tried to copy the range of data A1:E1000 in "MasterList" and paste it in new sheet in the target workbook. However, the formatting was lost & I couldn't get the .PasteSpecial function to work either.

I'm using Excel 2010.