Can someone give me code to copy a worksheet from one workbook to a new
workbook? I need to copy 2 sheets. When I try to copy one
(Application..Worksheets("Data1").Copy) it seems to automatically
create a new workbook called "Book1". When I try to copy the second
sheet it creates another workbook. I need to paste the second sheet
into book1 also.

Thanks,
rut

Hi rut,

Can someone give me code to copy a worksheet from one workbook to a new
workbook? I need to copy 2 sheets. When I try to copy one
(Application..Worksheets("Data1").Copy) it seems to automatically
create a new workbook called "Book1". When I try to copy the second
sheet it creates another workbook. I need to paste the second sheet
into book1 also.

try something like this:

Sub test()
Workbooks("Test.xls").Worksheets("Data1").Copy _
Befo=Workbooks("Book1.xls").Worksheets(1)
Workbooks("Test.xls").Worksheets("Data2").Copy _
Befo=Workbooks("Book1.xls").Worksheets(1)
End Sub

--
Greetings,
Monika Weber [Microsoft MVP für Excel]
http://www.jumper.ch (Office Help Desk)

So if I want to create a new workbook for the first sheet (don't use
Before) but I don't want to for the second sheet, how would that code
look? I'm trying to get away from referencing the new workbook by name
(ie, "book1") as I don't know that the user won't have Excel open and
another workbook already called book1.
I need to be able to use activeworkbook references.

Rut

Another way

worksheets(Array("Data1","Data2")).Copy

--
Regards,
Tom Ogilvy


"Monika Weber" wrote in message
...
Hi rut,

Can someone give me code to copy a worksheet from one workbook to a new
workbook? I need to copy 2 sheets. When I try to copy one
(Application..Worksheets("Data1").Copy) it seems to automatically
create a new workbook called "Book1". When I try to copy the second
sheet it creates another workbook. I need to paste the second sheet
into book1 also.

try something like this:

Sub test()
Workbooks("Test.xls").Worksheets("Data1").Copy _
Befo=Workbooks("Book1.xls").Worksheets(1)
Workbooks("Test.xls").Worksheets("Data2").Copy _
Befo=Workbooks("Book1.xls").Worksheets(1)
End Sub

--
Greetings,
Monika Weber [Microsoft MVP für Excel]
http://www.jumper.ch (Office Help Desk)

Activeworkbook.worksheets(Array("Sheet1","Sheet2") ).copy

creates a new workbook containing a copy of sheet1 and sheet2

The copy is now the active workbook.

Activeworkbook.SaveAs "MyNewBook.xls"

for example.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
So if I want to create a new workbook for the first sheet (don't use
Before) but I don't want to for the second sheet, how would that code
look? I'm trying to get away from referencing the new workbook by name
(ie, "book1") as I don't know that the user won't have Excel open and
another workbook already called book1.
I need to be able to use activeworkbook references.

Rut

Also, the sheets don't appear right after each other in the existing
book, ie there are other sheets between "Data1" and "T-data"

Rut

Public Sub Test1()


Dim wkbk As Workbook
Dim wkbk1 As Workbook
Dim sh As Worksheet
Dim sh1 As Worksheet
Set wkbk = ActiveWorkbook
'Set wkbk1 = Workbooks.Open("C:\MyFolder\MyNewBook.xls")
Set wkbk1 = Workbooks("Book3")
For Each sh In wkbk1.Worksheets
Set sh1 = Nothing
On Error Resume Next
Set sh1 = Worksheets(sh.Name)
On Error GoTo 0
If Not sh1 Is Nothing Then
sh1.Name = sh1.Name & "_delete"
End If
Next
wkbk1.Worksheets.Copy After:= _
wkbk.Worksheets(wkbk.Worksheets.Count)
For Each sh In wkbk.Worksheets
If Right(sh.Name, 7) = "_delete" Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next
End Sub

--
Regards,
Tom Ogilvy



wrote in message
oups.com...
OK, that works. Let me ask one more related question. If I want to open
this new book up and copy both sheets back into another workbook that
already has these same sheets (I want to replace existing sheets), how
would that look?

Thanks,
Rut

If you want the sheets to be in the same order, then my code doesn't do
that.

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Also, the sheets don't appear right after each other in the existing
book, ie there are other sheets between "Data1" and "T-data"

Rut

This should do what you want.

Public Sub Test22()


Dim wkbk As Workbook
Dim wkbk1 As Workbook
Dim sh As Worksheet, sName As String
Dim sh1 As Worksheet, sh2 As Worksheet
Set wkbk = ActiveWorkbook
'Set wkbk1 = Workbooks.Open("C:\MyFolder\MyNewBook.xls")
Set wkbk1 = Workbooks("Book3")
For Each sh In wkbk1.Worksheets
Set sh1 = Nothing
On Error Resume Next
Set sh1 = wkbk.Worksheets(sh.Name)
On Error GoTo 0
If Not sh1 Is Nothing Then
sName = sh.Name
sh.Copy After:=sh1
Set sh2 = ActiveSheet
Application.DisplayAlerts = False
sh1.Delete
Application.DisplayAlerts = True
sh2.Name = sName
Else
sh.Copy After:=wkbk.Worksheets( _
wkbk.Worksheets.Count)
End If
Next

End Sub


--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Also, the sheets don't appear right after each other in the existing
book, ie there are other sheets between "Data1" and "T-data"

Rut

Even though this doesn't do what you want,
There was a missing qualifier, so for completeness:

Public Sub Test1()


Dim wkbk As Workbook
Dim wkbk1 As Workbook
Dim sh As Worksheet
Dim sh1 As Worksheet
Set wkbk = ActiveWorkbook
Set wkbk1 = Workbooks.Open("C:\MyFolder\MyNewBook.xls")
For Each sh In wkbk1.Worksheets
Set sh1 = Nothing
On Error Resume Next
Set sh1 = wkbk.Worksheets(sh.Name)
On Error GoTo 0
If Not sh1 Is Nothing Then
sh1.Name = sh1.Name & "_delete"
End If
Next
wkbk1.Worksheets.Copy After:= _
wkbk.Worksheets(wkbk.Worksheets.Count)
For Each sh In wkbk.Worksheets
If Right(sh.Name, 7) = "_delete" Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...

Public Sub Test1()


Dim wkbk As Workbook
Dim wkbk1 As Workbook
Dim sh As Worksheet
Dim sh1 As Worksheet
Set wkbk = ActiveWorkbook
'Set wkbk1 = Workbooks.Open("C:\MyFolder\MyNewBook.xls")
Set wkbk1 = Workbooks("Book3")
For Each sh In wkbk1.Worksheets
Set sh1 = Nothing
On Error Resume Next
Set sh1 = Worksheets(sh.Name)
On Error GoTo 0
If Not sh1 Is Nothing Then
sh1.Name = sh1.Name & "_delete"
End If
Next
wkbk1.Worksheets.Copy After:= _
wkbk.Worksheets(wkbk.Worksheets.Count)
For Each sh In wkbk.Worksheets
If Right(sh.Name, 7) = "_delete" Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next
End Sub

--
Regards,
Tom Ogilvy



wrote in message
oups.com...
OK, that works. Let me ask one more related question. If I want to open
this new book up and copy both sheets back into another workbook that
already has these same sheets (I want to replace existing sheets), how
would that look?

Thanks,
Rut