problem copy/pasting pages.

[rutledj]

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

 

[Monika Weber]

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 _
Before:=Workbooks("Book1.xls").Worksheets(1)
Workbooks("Test.xls").Worksheets("Data2").Copy _
Before:=Workbooks("Book1.xls").Worksheets(1)
End Sub

 

[Norman Jones]

Hi Rutledj,

To copy multiple sheets between Book1 and Book2, try:

Sub sTester02()
Workbooks("Book1").Sheets(Array("Sheet2", "Sheet3")).Copy _
before:=Workbooks("Book2").Sheets(1)
End Sub

When I try to copy one
(Application..Worksheets("Data1").Copy) it seems to automatically
create a new workbook called "Book1". --

From Help:
'--------------------------------------------------->>
expression.Copy(Before, After)

Before Syntax 3: Optional Variant. The sheet before which the copied sheet
will be placed. You cannot specify Before if you specify After.

After Optional Variant. The sheet after which the copied sheet will be
placed. You cannot specify After if you specify Before.

Remarks

If you don't specify either Before or After, Microsoft Excel creates a new
workbook that contains the copied sheet.

'---------------------------------------------------<<

 

[rutledj]

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

 

[Tom Ogilvy]

Another way

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

 

[Tom Ogilvy]

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.

 

[rutledj]

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

 

[rutledj]

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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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