VBA to create sheets cycling through a data validation in a cell

[muhleebbin]

Can someone assist me in creating a VBA script that will copy and paste from a Master Sheet the 12 sheets that should be created from the data validation that is in cell C6. C6 has the months of the year. Basically it would copy and create the 12 sheets for each month. Ideally it would take the values in B2, C5 and the month and also rename the sheets their respective values (i.e. "Location Department January" etc). Secondary ask is that A1:I10 would be values only whereas the rest of the sheet would include the formulas that are in cells I11:I60.

Oh also if the script is run, it will create the additional sheets (2-13) can there be a subsequent script that would delete the additional sheets? 'Master Sheet' is where the main sheet lies and where the script would run from.

 

[muhleebbin]

ok my dumb butt actually realized this site is a tool so actually looked up an answer that gets me at least started on the answer.

This thread was the closest to the answer that I'm looking for but would like to tweak it a little.

VBA Code that Loops Through Validation List and Copies and Pastes Sheet into New Workbooks, dynamically named

Hello, I have the following code and I keep receiving "Object Required" every time I run it. Can anyone tell me where I am going wrong? I am relatively new to VBA. Thanks in advance! Sub DashboardToPDF() Dim FolderName As String, fName As String Dim inputRange As Range, r As Range, c As Range...

www.mrexcel.com

Taking the code from the link:

Rather than save each sheet as a workbook, is it possible to create a new workbook but with the 12 sheets for each month? Also naming the sheets per the values in B2, C5 and C6. This would eliminate the need for the last ask of needing a script to delete additional sheets since everything will be in a new workbook.

Sub DashboardToPDF()
Dim FolderName As String, fName As String
Dim inputRange As Range, r As Range, c As Range
Dim newWB As Workbook, currentWB As Workbook
Dim newS As Worksheet, currentS As Worksheet


Application.ScreenUpdating = False
'''' Open file dialog and choose folder
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    If .Show = True Then
        FolderName = .SelectedItems(1) & "\"
    Else
        Exit Sub
    End If
End With

'''' Location of DataValidation cell
Set r = Worksheets("Formatted Template").Range("A1")
'''' Get DataValidation values
Set inputRange = Evaluate(r.Validation.Formula1)



        
'''' Loop through DataValidation list
For Each c In inputRange
    r.Value = c.Value
    fName = c.Value


'Copy the data you need

ActiveSheet.Range("A:O").Select
Selection.Copy

'Create a new file that will receive the data
Set newWB = Workbooks.Add
    With newWB
        Set newS = newWB.Sheets("Sheet1")
        newS.Range("A1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False

        'Save in CSV
        Application.DisplayAlerts = False
        .SaveAs Filename:=FolderName & fName, FileFormat:=52
        Application.DisplayAlerts = True
    End With

   

Next c
Application.ScreenUpdating = True

End Sub

 

[muhleebbin]

Update to code however it only records June's sheet and no others....

Sub LoopThroughDataValidationList()

Application.ScreenUpdating = False

Dim rng As Range
Dim dataValidationArray As Variant
Dim i As Integer
Dim rows As Integer
Dim aname1 As String
Dim aname2 As String

aname1 = Workbooks("Budget Tracking 2022 v2").Sheets("Lists").Range("P1").Value
aname2 = Workbooks("Budget Tracking 2022 v2").Sheets("Master Sheet").Range("C5").Value

Workbooks.Add

'Set the cell which contains the Data Validation list
Set rng = Workbooks("Budget Tracking 2022 v2").Sheets("Master Sheet").Range("C6")

'If Data Validation list is not a range, ignore errors
On Error Resume Next


'Create an array from the Data Validation formula, without creating
'a multi-dimensional array from the range
rows = Range(Replace(rng.Validation.Formula1, "=", "")).rows.Count
ReDim dataValidationArray(1 To rows)

For i = 1 To rows
    dataValidationArray(i) = _
        Range(Replace(rng.Validation.Formula1, "=", "")).Cells(i, 1)
Next i


'If not a range, then try splitting a string
If Err.Number <> 0 Then
    Err.Clear
    dataValidationArray = Split(rng.Validation.Formula1, ",")
End If


'Some other error has occured so exit sub
If Err.Number <> 0 Then Exit Sub

'Reinstate error checking
On Error GoTo 0

'Loop through all the values in the Data Validation Array
For i = LBound(dataValidationArray) To UBound(dataValidationArray)

    'Change the value in the data validation cell
    rng.Value = dataValidationArray(i)
    
    'Force the sheet to recalculate
    Application.Calculate
    
        Workbooks("Budget Tracking 2022 v2").Sheets("Master Sheet").Copy After:=Sheets(Sheets.Count)
        On Error Resume Next
        ActiveSheet.Tab.ColorIndex = xlColorIndexNone
        ActiveSheet.Name = Workbooks("Budget Tracking 2022 v2").Sheets("Lists").Range("Q1").Value
        On Error GoTo 0
          
Next i

ActiveWorkbook.SaveAs Filename:=aname1 & " - " & aname2 & ".xlsx"

Application.ScreenUpdating = True

End Sub