View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default problem copy/pasting pages.

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