need macro to copy worksheets into individual workbooks

[neov]

Hi all. Thanks in advance.

Have a workbook with approximately 25 worksheets in it.

I want to copy/paste/value each worksheet and save it as it's own workbook.

My code doesn't work, it hangs up on ws.copy every single time, on Excel 2007 and prior versions.

Does anyone have working code for something like this?

FYI, my current code is : - I had posted in another thread, but the title was misleading!

Sub SaveWS()
Dim wb As Workbook
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Copy
Set wb = ActiveWorkbook
wb.SaveAs "t:\dir1\expenses\" & ws.Name & ".xls"
wb.Close False
Next ws

End Sub

 

[VoG]

Perhaps if the book/sheet was/were protected and to convert formulas to values:

Sub SaveWS()
Dim wb As Workbook
Dim ws As Worksheet
ThisWorkbook.Unprotect Password:="xyz"
For Each ws In ThisWorkbook.Worksheets
    ws.Unprotect Password:="xyz"
    ws.Copy
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
    Set wb = ActiveWorkbook
    wb.SaveAs "T:\dir1\expenses\" & ws.Name & ".xls"
    wb.Close False
    ws.Protect Password:="xyz"
Next ws
ThisWorkbook.Protect Password:="xyz"
End Sub

 

[neov]

nothing is protected

still getting "Run Time Error '1004'
Method 'Copy' of object '_Worksheet' Failed

 

[VoG]

Are any of these sheets hidden?

 

[neov]

there are several hidden sheets, but the macro crashes before it does anything at all

 

[KevinRich]

Try search "Split Excel workbook" on google.
the first result is "Excel Split workbook into new file for each worksheethttp://www.mrexcel.com/archive2/65500/75857.htm".

 

[neov]

thx Kevin, this is the first code that has worked for me

Sub SplitSheets()
Dim W As Worksheet
For Each W In Worksheets
W.SaveAs ActiveWorkbook.Path & "/" & W.Name
Next W
End Sub

Now I just need a macro to copy/paste-special value an entire sheet and set the print attributes, and I'll be all set - should be able to figure that out by recording.

Thanks again.

 

[Norie]

neov

Have you actually checked what the last code you posted does?

I think you might find it might save a little bit more than the individual sheets.

 

[neov]

doh - it appears to save the entire file, but with a different worksheet name every time....so I'm back to square one!

 

[thomastapple]

Yes, same thing for me. It copied the entire worksheet into non-excel files with the name of each worksheet. I am still searching for a solution.