Copy Worksheets from one book to another?

[Jonx]

Hello, I was curious if I have two workbooks, and I would like to cop
worksheet 1 from book 1 to worksheet 1 on book 2, how would I go abou
doing this? How would I execute this code in VB?

thanks

 

[Frank Kabel]

Hi
try recording a macro while doing the following manually:
- assumption: both workbooks are opened
- activate the source workbook
- select the tab and right-click on the tab name
- choose Move7Copy
- select the target workbook + location

 

[Jonx]

well there is an actual method for copying the worksheets. It goe
something like:

ThisApplication.ActiveWorkbook.Sheets(1).Copy( _
After:=ThisApplication.ActiveWorkbook.Sheets(3))


which will copy worksheet 1 and place it after worksheet 3 from th
same workbook. I tried playing witrh the code to copy it on differen
workbooks, and I got an error saying copy method faile

 

[Tom Ogilvy]

The sheet already exists in both workbooks?
Dim rng1 as Range, rng2 as Range
set rng1 = workbooks("book1.xls").worksheets(1).Cells
set rng2 = workbooks("Book2.xls").worksheets(1).Cells
rng1.copy Destination:=rng2

 

[Tom Ogilvy]

If the sheet doesn't already exist in the second workbook:

With Workbooks("Book2.xls")
Workbooks("Book1.xls").worksheets(1).copy After:= _
.Worksheets(.worksheets.count)
End With

copies it to the end of Book2.xls

 

[Jonx]

Thanks To