Macro for creating new workbooks from 1 workbook with multiple sheet

bj6264

Board Regular
Joined
May 23, 2010
Messages
50
Hello

Can anyone help with a simple macro for creating new workbooks from 1 workbook that has about 50 sheets?

Thanks
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Thanks but this isn't quite what I want. I want to make all worksheets in 1 exisiting workbook into new workbooks - nothing to do with months.
 
Upvote 0
Upvote 0
Thanks for your reply but I'm a real novice to VBA and your answers are way above my level. I think this may be a manual task for me.:eek:
 
Upvote 0
Open de VBA editor, insert a Module and paste this code:
http://www.mrexcel.com/forum/<wbr>showthread.php?t=416894
Code:
Sub MoveSheetsToWorkbooks()
Dim Ws As Worksheet, strFilepath As  String

    strFilepath = ThisWorkbook.Path &  "\"
    
    Application.ScreenUpdating =  False
    For Each Ws In ThisWorkbook.Worksheets 'loop through  the worksheets
        Ws.Copy 'and copy  the sheet to a new workbook
         ActiveWorkbook.SaveAs strFilepath &  ActiveSheet.Name
         ActiveWorkbook.Close False
    Next
     Application.ScreenUpdating = True
    MsgBox "All Done",  vbExclamation + vbOKOnly
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top