Parse Functions‎ > ‎

Sheets to WB(1)

  1. One workbook with many sheets
  2. Need a separate workbook for each sheet so they can be sent to various departments without sending whole wb.
  3. Macro is run frequently, so each time it is run the workbooks created include the date in the name.
  4. Original workbook has formulas, created workbooks should be formula-free, flat values only.
  5. NOTE:  The SaveAs command in the macro is longer for Excel 2007 or newer.  Choose the right SaveAs command for your Excel version:


Sub SheetsToBooks()
'Jerry Beaucaire  (10/22/2009)
'Creates a separate workbook for each sheet, saved with sheetname + Date
Dim ws As Worksheet, savePath As String, MyStr As String

savePath = "C:\DailyReports\"   'do not forget the closing \ in this string

    For Each ws In ThisWorkbook.Worksheets
        MyStr = ws.Name & " " & Format(Date, "mm-dd-yy")
        ws.Copy        'copies sheet to new workbook
        Cells.Copy     'removing all formulas
        Range("A1").PasteSpecial xlPasteValues
        ActiveWorkbook.SaveAs Filename:=savePath & MyStr, FileFormat:=xlNormal
        'ActiveWorkbook.SaveAs SvPath & MyStr & ".xlsx", 52     'Use this for Excel 2007 or greater
        ActiveWorkbook.Close False
    Next ws

End Sub

4. To have this code only run on certain sheets, use this to name the sheets specifically:


    For Each ws In Sheets(Array("Data1", "Data2", "Data3"))

5. To have this code EXCLUDE certain sheets specifically:


    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Data1" And ws.Name <> "Options" Then

            'other code here

        End If
    Next ws

Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!