saving sheets as individual files

[cav~firez22]

Hi all,

i have this code,

Sub Test1() 
Application.ScreenUpdating = False 
Dim ws As Worksheet, strPath$, strSheetName$ 
strPath = ThisWorkbook.Path & "\" 
For Each ws In Worksheets 
strSheetName = ws.Name 
Workbooks.Add 1 
ws.Cells.Copy Cells 
Sheets(1).Name = strSheetName 
ActiveWorkbook.SaveAs strPath & strSheetName & ".xls" 
Next 
Application.ScreenUpdating = True 
End Sub

What would i do to make it so once it has saved a file, it closes that file and continues on.

Currently, it just keeps them open, and the workbook it is going through has over 100 sheets, there fore i end up with 100 excel windows open.??

any ideas?

 

[ktab]

try it like this:

Sub Test1()
Dim ws As Worksheet, strPath$, New_WB As Workbook

Application.ScreenUpdating = False
strPath = ThisWorkbook.Path & "\"

For Each ws In ThisWorkbook.Sheets
    ws.Copy
    Set New_WB = ActiveWorkbook
    With New_WB
        .SaveAs strPath & ws.Name & ".xls"
        .Close
    End With

Next
Application.ScreenUpdating = True
End Sub