How to copy a worksheet from one workbook to another in VBA ?

  • Thread starter Thread starter Rasha
  • Start date Start date
R

Rasha

Hello world,

How to copy a worksheet from one workbook to another ?

I have been trying the whole afternoon without success.

Help please !
 
Rasha,

With your both workbooks open.
Right - Click the worksheet tab and choose "Move or Copy".
Select the appropriate workbook from the "To Book" list.
You can position where the worksheet will be put using the
"Before sheet" selection box.
Check off the "Create a copy" box and hit OK.

Dan E
 
Welcome to the club Dan, that happens to me all the time,
or the reverse as well when I only go after the subject
and hardly read the body <g>
 
Rasha,

The following worked for me.

Dim My2 As Workbook, Sh1 As Worksheet
Set Sh1 = ThisWorkbook.Sheets("Sheet1")
Set My2 = Workbooks("Test.xls")

Sh1.Copy After:=My2.Sheets(My2.Sheets.Count) 'Places Sheet1 at the end of Test.xls

Both workbooks need to be open

Dan E
 
Your line:
wSht.Copy after:=wBk.Sheets(Sheets.Count)

should be:
wSht.Copy after:=wBk.Sheets(wBk.Sheets.Count)

If the destination book has less sheets than the origin
your code would produce an error. The only reason I
know is I did the same thing. Sheets.Count will count
the sheets in the active workbook which will be the
origin.

Dan E
 
Assuming that you're already in the WorkBook that contains the sheet
to copy; that your destination workbook is already open.

Sub CopySheet()
Dim wSht As Worksheet
Dim wBk As Workbook
Set wBk = Workbooks("YourDestinationWorkBook.xls")
Set wSht = Sheets("YourSheetName")
wSht.Copy after:=wBk.Sheets(Sheets.Count)
End Sub

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul
 
Your line:
wSht.Copy after:=wBk.Sheets(Sheets.Count)

should be:
wSht.Copy after:=wBk.Sheets(wBk.Sheets.Count)

If the destination book has less sheets than the origin
your code would produce an error. The only reason I
Click to expand...
I didn't realize that. Thanks.

Paul
------------------
 
Back
Top