Print all combinations from a drop down list onto a pdf or a new excel sheet

[Ankit Chowdhary]

Hi Experts,

Need help to print all combinations from a drop down list onto a pdf or a new excel sheet which will have all combinations in different sheets.

Just to explain. I have three sheets (1. NSV, 2. Volumes 3. NSV per ton) and have 2 data validation drop downs with the below combinations:

1. Arivia, Retail, Total
2. Core, FS, Total

Hence there are 9 combinations, and 3 sheets, so total of 27 pdfs should come up but all should be in one pdf file or we can also have 27 new sheets in a new excel file with all the combinations. Quick help is needed pls.

I was asked to refer to a solution but that is very different from my ask.. Request your help here as its very time consuming for me right now.

 

[Ankit Chowdhary]

Hi.. Pls help here pls. Its still not helping me.

 

[BSALV]

structure remained the same, now only looking for the 3 mentioned worksheets (and check if they exist).
The names of the new sheets is a combination of the original name + the 2 dropdowns.
The new workbook is saved as xlsx and as pdf (only if the original workbook has already been saved, otherwise no path !).

Sub split_Workbook()
     Application.ScreenUpdating = False
     Dim drop0, drop1, drop2, k, i&, j&, ws As Worksheet, s, sh, sp
     drop0 = Array("1. NSV", "2. Volumes", "3. NSV per ton")
     drop1 = Array("Core", "Arivia", "Total")                   ' option list in cell C2
     drop2 = Array("Retail", "FS", "Total")                     ' option list in cell C3
     Workbooks.Add

     For k = 0 To UBound(drop0)                                 'loop through the 3 specific worksheets
          On Error Resume Next
          Set ws = Nothing
          Set ws = ThisWorkbook.Sheets(CStr(drop0(k)))          'this workbook
          If ws Is Nothing Then MsgBox "worksheet " & drop0(k) & " does not exist in this workbook", vbCritical: Exit Sub
          On Error GoTo 0
          For i = 0 To 2
               For j = 0 To 2
                    ws.Copy after:=Sheets(Sheets.Count)
                    With ActiveSheet
                         .Range("C2").Value = drop1(i)          'an option of this datavalidation
                         .Range("C3").Value = drop2(j)          'an option of this datavalidation
                         .Name = ws.Name & ", " & drop1(i) & "-" & drop2(j)     'name the new sheet as combination of old sheetname & the 2 datavalidations
                         If k + i + j = 0 Then                  'just in the first loop
                              Application.DisplayAlerts = False
                              Sheets(1).Delete                  'delete the initial and empty worksheet
                              Application.DisplayAlerts = True
                         End If
                    End With
               Next
          Next
     Next
     Application.ScreenUpdating = True

     ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Chowdhary_" & Format(Now, "yymmddhhmmss"), 51     'save new workbook as ... with timestamp

     s = ""
     For Each sh In ActiveWorkbook.Sheets
          s = s & vbLf & sh.Name
     Next
     sp = split(Mid(s, 2), vbLf)
     Sheets(sp).Select
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\Chowdhary_" & Format(Now, "yymmddhhmmss"), OpenAfterPublish:=True     ' save new workbook as pdf with timestamp
End Sub

 

[Ankit Chowdhary]

Thank you so much BSALV. It worked..