Parse excel sheets into new workbooks

[andream02]

Hello! I have a file that contains the same worksheets each month - approx 15. Each month I end up copying the sheets into new workbooks and saving them so I can email them out individually.

I'm hoping someone could help me with the VBA code required to extract the sheets into new workbooks and save them, as the tab name, into the desired location on my C: Drive. Its important that the code translates the data from the original sheet into the new workbook. I've tried code that creates the tab in a new workbook, but the cells are blank.

Any and all help is much appreciated.

Thank you!

 

[VoG]

Try

Sub savesheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Copy
    With ActiveWorkbook
        .SaveAs Filename:=ws.Name & Format(Date, "ddmmyyyy") & ".xls"
        .Close
    End With
Next ws
End Sub

 

[andream02]

YOU ARE AMAZING!!! Such simple code!!! Tears literally came to my eyes! Thank you so so much!

 

[Norie]

Something like this perhaps.

Sub SplitWB()
Dim wbNew As Workbook 
Dim wbThis As Workbook 
Dim ws As Worksheet
Dim strFileName As String

    Set wbThis =ThisWorkbook

    For Each ws In wbThis.Worksheets

         strFileName=ws.Name

         ws.Copy 

         Set wbNew = ActiveWorkbook

          wbNew.SaveAs "C:\"& strFileName

          wbNew.Close False 

     Next ws

End Sub

Change the save path/folder needed.

 

[andream02]

Hopefully a simple and quick question regarding the file name and date - how do I insert a space between the tab name and the date? right now I see the following:

Finance 4114923062011

I would like to see:

Finance 41149 - 23062011

Wasn't sure if I'm able to concatenate the code by adding &" -" after ws. Name.
.SaveAs Filename:=ws.Name & Format(Date, "ddmmyyyy") & ".xls"

Thanks!

 

[VoG]

Try

        .SaveAs Filename:=ws.Name & " - " & Format(Date, "ddmmyyyy") & ".xls"