Macro to separate worksheets into their own workbook

[Jeffrey Green]

I get 1 workbook with a dozen or so worksheets, and I really need each sheet to be its own workbook, with the name of the book just being the tab name. Is there a macro out there to do this?


Thoughts?

 

[VoG]

Maybe like this

Sub SheetToBook()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Copy
    With ActiveWorkbook
        .Name = ActiveSheet.Name & ".xls"
        .Close True
    End With
Next ws
End Sub

 

[Jeffrey Green]

Thanks, but I get this now:


Compile error

Can't assign to read-only property for the line

.Name = ActiveSheet.Name & ".xls"

 

[VoG]

Sorry, it should be

Sub SheetToBook()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.workshets
    ws.Copy
    With ActiveWorkbook
        .SaveAs Filename:=ActiveSheet.Name & ".xls"
        .Close True
    End With
Next ws
End Sub

 

[Jeffrey Green]

, Now I get:

Run-time error '438"

Object doesn't support this property or method.


Does it matter that I am in 2003?

I can use 2007, that isn't a problem . . .thanks

 

[Jeffrey Green]

Error is on line with

For Each ws In ActiveWorkbook.workshets

 

[Jeffrey Green]

Never mind, thanks. I saw the problem when I pasted it in a reply.

WORKSHEET was spelled wrong!

Thansk