VBA coding for copying individual worksheets and saving as individual workbooks

[Purple_Mona]

Hi All,

I am currently working on a macro that takes each worksheets from a workbook, copies it as a separate workbook and saves it using that particular worksheet's name. For example, I have a report with various departments as separate worksheets (Admin, Finance, Legal, etc). I want to copy the 'Admin' worksheet as a separate workbook and save it as Admin.xls.

Probably an easy thing to do - but I haven't used VBA for years and I'm extremely rusty. Here's the code I've been trying to use - but when it gets onto the 2nd worksheet (ie. Finance, from the above example), it keeps throwing up an error of "Run Time Error '9': Subscript out of range." Can anyone help? Please?

Thanks.

Sub Separate_WkSheet()

Dim n As Integer

For n = 1 To Worksheets.Count
Worksheets.Copy
ChDir "C:\Users\Main\Documents\Macros\Test_Folder"
ActiveWorkbook.SaveAs Filename:=Sheets.Name
ActiveWorkbook.Close
Workbooks("Save_As_Worksheets").Activate
Next n


End Sub

 

[wigi]

Please try the untested:

Sub Separate_WkSheet()

    Dim ws As Worksheet, s As String
    
    c0 = "C:\Users\Main\Documents\Macros\Test_Folder\"
    
    Application.ScreenUpdating = False


    For Each ws In ActiveWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs c0 & ws.Name, 56
        ActiveWorkbook.Close 0
    Next


End Sub

Can you please use

 tags when you paste code on the forum?
Code tags format the code making it easier to read and hence follow the logic of the code.


You can use [CODE] tags in this way: 


Add the word [COLOR=blue][B][PLAIN][code=rich][/PLAIN][/B][/COLOR] before the first line of code, and
add the word [COLOR=blue][B][PLAIN]

[/PLAIN][/B][/COLOR] after the last line of code.


Thanks for your consideration.

 

[Purple_Mona]

Works great. Thank you.