Copy and paste sheets between workbook

G

Guest

I got "Subscript out of range" error when I used the above code. That I want
is: I got a binder that contain two worksbooks. My first workbook have a
command button. When I press this botton it suppose that the macro open the
second workbooks (until here my code work fine) and copy the sheet
"AssessmentTemplate". Then paste the sheet to the first workbook after it
last sheet. My error is produced in the line that is coding the copy. Thanks
for any help.

stFileName = "AssessmentTemplate.xls"
stPath = ThisWorkbook.Path
stFullName = stPath & "\" & stFileName
Set Wkb = Workbooks.Open(Filename:=stFullName)

Wkb.Sheets("AssessmentTemplate").Select
With Selection
.Copy After:=Workbooks("MacroTemplate").Sheets(2) 'Error message with
line
End With
 
D

Dave Peterson

Sheets("AssessmentTemplate").Copy _
After:=Workbooks("MacroTemplate.xls").Sheets(2)

(I'd include the extension. It's safer.)
 
G

Guest

stFileName = "AssessmentTemplate.xls"
stPath = ThisWorkbook.Path
stFullName = stPath & "\" & stFileName
Set Wkb = Workbooks.Open(Filename:=stFullName)

Wkb.Sheets("AssessmentTemplate").Select
With ActiveSheet '<== change to reflect sheet, not range
.Copy After:=Workbooks("MacroTemplate.xls").Sheets(2) '<= add .xls
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding out pathname 1
open workbook problem 2
Copy two sheets from workbook & create new workbook 8
Lotus Notes CreateObject 1
Open four Wbooks, copy four columns from each to Master Wbook 16
Copy columns and paste with blank column between 5
Copying cells and formula from closed workbook to all sheets in open workbook 0
Copy and paste sheets to new workbook 14

Top