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

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

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