Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving sheets to another workbook
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+ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving sheets to another workbook
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 -- HTH... Jim Thomlinson "affordsol" wrote: 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+ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving Data between sheets in the same workbook and moving data between Workbooks. | Excel Worksheet Functions | |||
moving data between workbook sheets | Excel Discussion (Misc queries) | |||
Moving Sheets within a workbook | Excel Worksheet Functions | |||
Moving Macros with Sheets to New Workbook | Excel Programming | |||
Moving a variable number of Sheets to another Workbook | Excel Programming |