Copy Worksheets from one book to another?

J

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
 
F

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
 
J

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
 
T

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
 
T

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
 
Top