|
SaveAs without closing original workbook
Hi All,
I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
SaveAs without closing original workbook
I forgot to mention that the workbook to be cloned contains the cloning Sub,
so Thisworkbook is to be cloned! Stefio Stefi ezt *rta: Hi All, I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
SaveAs without closing original workbook
Adapt something like the following, if testing in a new wb first run Sub
origName() Sub origName() ThisWorkbook.SaveAs "TestOrig.xls" End Sub Sub test() Dim sPath As String Dim bKeepOpen Const cOrigName As String = "TestOrig.xls" ' << change to the default name bKeepOpen = True If ThisWorkbook.Name = cOrigName Then ThisWorkbook.Save sPath = ThisWorkbook.Path & "\" & _ Replace(ThisWorkbook.Name, ".xls", "", , , vbTextCompare) & "_" Application.DisplayAlerts = False For i = 1 To 3 ThisWorkbook.SaveAs sPath & MonthName(i, True) & ".xls" Next ThisWorkbook.SaveAs cOrigName If bKeepOpen Then For i = 1 To 3 Workbooks.Open sPath & MonthName(i, True) & ".xls" Next End If End If Application.DisplayAlerts = True End Sub Save with as many names as you need, then re-save with the original name, optionally reopen the others. Regards, Peter T "Stefi" wrote in message ... I forgot to mention that the workbook to be cloned contains the cloning Sub, so Thisworkbook is to be cloned! Stefio "Stefi" ezt rta: Hi All, I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
SaveAs without closing original workbook
Thanks Peter, I'll test it tomorrow!
Stefi Peter T ezt *rta: Adapt something like the following, if testing in a new wb first run Sub origName() Sub origName() ThisWorkbook.SaveAs "TestOrig.xls" End Sub Sub test() Dim sPath As String Dim bKeepOpen Const cOrigName As String = "TestOrig.xls" ' << change to the default name bKeepOpen = True If ThisWorkbook.Name = cOrigName Then ThisWorkbook.Save sPath = ThisWorkbook.Path & "\" & _ Replace(ThisWorkbook.Name, ".xls", "", , , vbTextCompare) & "_" Application.DisplayAlerts = False For i = 1 To 3 ThisWorkbook.SaveAs sPath & MonthName(i, True) & ".xls" Next ThisWorkbook.SaveAs cOrigName If bKeepOpen Then For i = 1 To 3 Workbooks.Open sPath & MonthName(i, True) & ".xls" Next End If End If Application.DisplayAlerts = True End Sub Save with as many names as you need, then re-save with the original name, optionally reopen the others. Regards, Peter T "Stefi" wrote in message ... I forgot to mention that the workbook to be cloned contains the cloning Sub, so Thisworkbook is to be cloned! Stefio "Stefi" ezt *rta: Hi All, I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
SaveAs without closing original workbook
Use SaveCopyAs
See the VBA help -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Stefi" wrote in message ... Thanks Peter, I'll test it tomorrow! Stefi Peter T ezt *rta: Adapt something like the following, if testing in a new wb first run Sub origName() Sub origName() ThisWorkbook.SaveAs "TestOrig.xls" End Sub Sub test() Dim sPath As String Dim bKeepOpen Const cOrigName As String = "TestOrig.xls" ' << change to the default name bKeepOpen = True If ThisWorkbook.Name = cOrigName Then ThisWorkbook.Save sPath = ThisWorkbook.Path & "\" & _ Replace(ThisWorkbook.Name, ".xls", "", , , vbTextCompare) & "_" Application.DisplayAlerts = False For i = 1 To 3 ThisWorkbook.SaveAs sPath & MonthName(i, True) & ".xls" Next ThisWorkbook.SaveAs cOrigName If bKeepOpen Then For i = 1 To 3 Workbooks.Open sPath & MonthName(i, True) & ".xls" Next End If End If Application.DisplayAlerts = True End Sub Save with as many names as you need, then re-save with the original name, optionally reopen the others. Regards, Peter T "Stefi" wrote in message ... I forgot to mention that the workbook to be cloned contains the cloning Sub, so Thisworkbook is to be cloned! Stefio "Stefi" ezt *rta: Hi All, I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
SaveAs without closing original workbook
Much better, I didn't think of that!
Regards, Peter T "Ron de Bruin" wrote in message ... Use SaveCopyAs See the VBA help -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Stefi" wrote in message ... Thanks Peter, I'll test it tomorrow! Stefi "Peter T" ezt rta: Adapt something like the following, if testing in a new wb first run Sub origName() Sub origName() ThisWorkbook.SaveAs "TestOrig.xls" End Sub Sub test() Dim sPath As String Dim bKeepOpen Const cOrigName As String = "TestOrig.xls" ' << change to the default name bKeepOpen = True If ThisWorkbook.Name = cOrigName Then ThisWorkbook.Save sPath = ThisWorkbook.Path & "\" & _ Replace(ThisWorkbook.Name, ".xls", "", , , vbTextCompare) & "_" Application.DisplayAlerts = False For i = 1 To 3 ThisWorkbook.SaveAs sPath & MonthName(i, True) & ".xls" Next ThisWorkbook.SaveAs cOrigName If bKeepOpen Then For i = 1 To 3 Workbooks.Open sPath & MonthName(i, True) & ".xls" Next End If End If Application.DisplayAlerts = True End Sub Save with as many names as you need, then re-save with the original name, optionally reopen the others. Regards, Peter T "Stefi" wrote in message ... I forgot to mention that the workbook to be cloned contains the cloning Sub, so Thisworkbook is to be cloned! Stefio "Stefi" ezt rta: Hi All, I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
SaveAs without closing original workbook
Thanks Ron and Peter! Peter's solution works, but Ron's one is really much
nicer! Stefi Peter T ezt *rta: Much better, I didn't think of that! Regards, Peter T "Ron de Bruin" wrote in message ... Use SaveCopyAs See the VBA help -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Stefi" wrote in message ... Thanks Peter, I'll test it tomorrow! Stefi "Peter T" ezt *rta: Adapt something like the following, if testing in a new wb first run Sub origName() Sub origName() ThisWorkbook.SaveAs "TestOrig.xls" End Sub Sub test() Dim sPath As String Dim bKeepOpen Const cOrigName As String = "TestOrig.xls" ' << change to the default name bKeepOpen = True If ThisWorkbook.Name = cOrigName Then ThisWorkbook.Save sPath = ThisWorkbook.Path & "\" & _ Replace(ThisWorkbook.Name, ".xls", "", , , vbTextCompare) & "_" Application.DisplayAlerts = False For i = 1 To 3 ThisWorkbook.SaveAs sPath & MonthName(i, True) & ".xls" Next ThisWorkbook.SaveAs cOrigName If bKeepOpen Then For i = 1 To 3 Workbooks.Open sPath & MonthName(i, True) & ".xls" Next End If End If Application.DisplayAlerts = True End Sub Save with as many names as you need, then re-save with the original name, optionally reopen the others. Regards, Peter T "Stefi" wrote in message ... I forgot to mention that the workbook to be cloned contains the cloning Sub, so Thisworkbook is to be cloned! Stefio "Stefi" ezt *rta: Hi All, I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
| All times are GMT +1. The time now is 11:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com