Moving sheets to another workbook

[Guest]

Hi!

I have a source workbook which creates 25 to 30 worksheets, each one's name
ends with "P1"

I want to move these sheets to an existing destination workbook, through a
vba sub residing in the source workbook

BUT :
if one or the source worksheet exists in the destination workbook, it
should be replaced by the new one


I've tried to code that, but Excel generates an error and closes.


Any idea for coding that ?

Thanks by advance,
Herve+

 

[Guest]

try this...

Sub Test()
Dim wbkSource As Workbook
Dim wbkDestination As Workbook
Dim wksSource As Worksheet

On Error GoTo ErrorHandler
Application.DisplayAlerts = False
'Set your source
Set wbkSource = ThisWorkbook
Set wksSource = wbkSource.Sheets("Sheet1")

'Set your destination
On Error Resume Next
Set wbkDestination = Workbooks("ThatBook.xls")
On Error GoTo ErrorHandler
If wbkDestination Is Nothing Then
Set wbkDestination = Workbooks.Open("C:\Thatbook.xls")
End If

'You now have all of your souce and destination objects
If SheetExists(wksSource.Name, wbkDestination) Then
wbkDestination.Sheets(wksSource.Name).Delete
End If
wksSource.Copy wbkDestination.Sheets(1)

ErrorHandler:
Application.DisplayAlerts = True
End Sub

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function