Save specific sheets as separate workbooks

[Sir Babydum GBE]

Hi, long time no sea!

I would like to have Sheets 3, 4, 5, 6, 7 and 8 of a 10-sheet workbook saved as six individual workbooks (in the same location as wherever the current workbook is saved in). IE each new wb will consist of only the one sheet.

Columns B & C need to have all formulae converted to values

The Names that appear on the sheet tabs may be changed by the user, so i need vba to ignore them and use the hidden vba name for the sheets which is sheet3, sheet4 etc.

the filename for the sheet needs to be a date (yyyy/mm) a space and whatever is in cell B2 of each of the sheets.

Any help would be greatly appreciated.

thanks

Sir BD

[Paul_Hossler]

I'd just do something simple like this

Option Explicit

Sub MakeSomeWorkbooks()
    Application.ScreenUpdating = False
    Application.CalculateFull
    CopySheet Sheet3
    CopySheet Sheet4
    CopySheet Sheet5
    CopySheet Sheet6
    CopySheet Sheet7
    CopySheet Sheet8
    Application.ScreenUpdating = True
End Sub




Private Sub CopySheet(ws As Worksheet)
    Dim wb As Workbook
    Dim sFile As String
    
    sFile = ws.Parent.Path & Application.PathSeparator & Format(Date, "yyyy-mm") & " " & ws.Range("B2").Text
    
    ws.Copy
    Set wb = ActiveWorkbook
    
    wb.SaveAs sFile, xlOpenXMLWorkbook
    wb.Close
    
    ThisWorkbook.Activate
End Sub

[Kenneth Hobs]

If you have a lot of formulas to convert to values, it would probably be faster for the macro to copy the workbook, change columns B and C as I did, and then work from there. That would also eliminate the extra save.

If don't have a lot of data, this change to Paul's routine should suffice.

Private Sub CopySheet(ws As Worksheet)    
  Dim sFile As String, r As Range
    
    sFile = ws.Parent.Path & Application.PathSeparator & Format(Date, "yyyy-mm") & " " & ws.Range("B2").Text
    
    ws.Copy
    With ActiveWorkbook
      .SaveAs sFile, xlOpenXMLWorkbook
      Set r = Intersect(.Worksheets(1).UsedRange, .Worksheets(1).Columns("B:C"))
      r.Value = r.Value
      .Save
      .Close
    End With
    
    ThisWorkbook.Activate
End Sub

[Paul_Hossler]

Yea, I forgot the B:C to values part

[Sir Babydum GBE]

Thanks both. That worked perfectly. Thanks for your time and sorry for the delay in my response.