VBA Code to copy selected tabs to new separate new workbooks, Hardcode, then Save As

[CDave14]

Hey team,
I have a master/template sheet that has multiple tabs some of which only apply in certain circumstances.
Could I please get some help in writing a VBA which moves Sheets [lets name them (S1, S2, S3...)] to a new book, hardcodes all the values then saves with a file name based on a cell values (A1 & B2).
E.g. I'd like to create a new book with S1 & S3 then hardcode and save and create another new book with S2 & S3 then hardcode and save, and so on.
Fairly new to VBA so let me know if you need more info

Thanks in advance.

 

[kevin9999]

How do you intend to 'select' which tabs you want to copy to a new workbook? Will they always be the same combination?
How will you 'build' the new file name from the values in cells A1 & B2, and are they on the 'master/template' sheet?
Do you want to save the new workbook to the same folder that the master workbook is in?

 

[CDave14]

They will be the same combination of sheets to copy to new book and the sheet names in the master will be static , so was planning to select/identify by the sheet name e.g. Copy S1 & S3 to a new book to create the new book

File name will be the same as the master with values in A1 (date) & B2 (identifier) added to the end on a menu sheet where the macro will be run from.

Cheers Kevin

 

[kevin9999]

File name will be the same as the master with values in A1 (date) & B2 (identifier) added to the end on a menu sheet where the macro will be run from.

That bit confuses me
What exactly is the "menu sheet"? Is this at the end of the master workbook, and do you intend to keep a running list of copies made?
Please answer these questions, but in the meantime I'll start putting together some code that I would use if I had a similar project.

 

[kevin9999]

The following is provided purely for demonstration purposes, showing the various steps I would take to do this kind of project. I get the impression that there's not dozens of possible combinations of sheets, therefore you could hardcode the actual sheet names for each macro & assign to different buttons perhaps? Otherwise, there are more complicated ways of selecting sheet combinations. I've kept the code deliberately simple to be easy to adjust to your actual requirements. Please try it on a copy of your workbook. This one looks to add sheets S1 and S3 to a new workbook.

Option Explicit
Sub Test_S1_and_S3()
    
    'Declare variables
    Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet, wb As Workbook
    Dim s As String, i As Long
    Set ws1 = Worksheets("master")
    Set ws2 = Worksheets("menu")
    
    'Build the new workbook name
    s = ActiveWorkbook.Name
    If InStr(s, ".") > 0 Then
       s = Left(s, InStr(s, ".") - 1)
    End If
    s = s & " " & Format(ws1.Range("A1"), "dd-mm-yyyy") & " " & ws1.Range("B2").Value
    
    'Select the sheets and copy to a new workbook
    With ThisWorkbook.Sheets(Array("S1", "S3"))     '<~~ *** change sheet names for other macros ***
        .Copy
    End With
    Set wb = ActiveWorkbook
    
    'Convert formulas to constants
    With wb.Sheets(1).UsedRange
        .Value = .Value
    End With
    With wb.Sheets(2).UsedRange
        .Value = .Value
    End With
    
    'Save the new workbook with a new name
    wb.SaveAs ThisWorkbook.Path & "\" & s
    
    'Add the newly created file name to the next available cell in column A of the menu sheet
    ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = s

End Sub

 

[CDave14]

Thanks Kev, this is super helpful. Very much appreciated.

 

[kevin9999]

Happy to help Dave, and welcome to the forum