Separating worksheets into new workbooks

[Rob V]

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]

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

 

[Ron de Bruin]

Hi Rob

I have copy a other macro on my site
http://www.rondebruin.nl/copy6.htm