Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Separating worksheets into new workbooks
Hi all,
I am using a macro posted here by Ron de Bruin (THANKS!) that separates worksheets into separate files, but I am having a minor issue with it... the macro is as follows: Sub test() Dim a As Integer Dim wb As Workbook Application.ScreenUpdating = False For a = 1 To ThisWorkbook.Worksheets.Count ThisWorkbook.Sheets(a).Copy Set wb = ActiveWorkbook wb.SaveAs "C:\Separate\" & wb.Sheets(1).Name & ".xls" wb.Close False Set wb = Nothing Next a Application.ScreenUpdating = True End Sub The macro works fine, but when I try to open the output files, I receive a "File already in Use" violation. I can open it in read only and then save it as something else, but wondering if there is something that can be added to the macro to fix this condition? Thanks, Rob |
#2
|
|||
|
|||
I don't think it's your macro.
Take a look at this FAQ that Debra Dalgleish shares: http://www.contextures.com/xlfaqApp.html#AlreadyOpen Rob V wrote: Hi all, I am using a macro posted here by Ron de Bruin (THANKS!) that separates worksheets into separate files, but I am having a minor issue with it... the macro is as follows: Sub test() Dim a As Integer Dim wb As Workbook Application.ScreenUpdating = False For a = 1 To ThisWorkbook.Worksheets.Count ThisWorkbook.Sheets(a).Copy Set wb = ActiveWorkbook wb.SaveAs "C:\Separate\" & wb.Sheets(1).Name & ".xls" wb.Close False Set wb = Nothing Next a Application.ScreenUpdating = True End Sub The macro works fine, but when I try to open the output files, I receive a "File already in Use" violation. I can open it in read only and then save it as something else, but wondering if there is something that can be added to the macro to fix this condition? Thanks, Rob -- Dave Peterson |
#3
|
|||
|
|||
Hi Rob
I have copy a other macro on my site http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Dave Peterson" wrote in message ... I don't think it's your macro. Take a look at this FAQ that Debra Dalgleish shares: http://www.contextures.com/xlfaqApp.html#AlreadyOpen Rob V wrote: Hi all, I am using a macro posted here by Ron de Bruin (THANKS!) that separates worksheets into separate files, but I am having a minor issue with it... the macro is as follows: Sub test() Dim a As Integer Dim wb As Workbook Application.ScreenUpdating = False For a = 1 To ThisWorkbook.Worksheets.Count ThisWorkbook.Sheets(a).Copy Set wb = ActiveWorkbook wb.SaveAs "C:\Separate\" & wb.Sheets(1).Name & ".xls" wb.Close False Set wb = Nothing Next a Application.ScreenUpdating = True End Sub The macro works fine, but when I try to open the output files, I receive a "File already in Use" violation. I can open it in read only and then save it as something else, but wondering if there is something that can be added to the macro to fix this condition? Thanks, Rob -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding certain cells in multiple worksheets in multiple workbooks | Excel Worksheet Functions | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
Linking Workbooks | Excel Worksheet Functions | |||
Multiple worksheets, multiple workbooks | Excel Worksheet Functions | |||
Workbooks...I'll try this again... | Excel Discussion (Misc queries) |