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.

 

[BSALV]

as starter because your explanation is vague
you can add a 3rd loop with the sheets to have 27 combinations

Sub comb()
     For Each it1 In Array("Arivia", "Retail", "Total")
          For Each it2 In Array("Core", "FS", "Total")
               Sheets.Add(After:=Sheets(Sheets.Count)).Name = it1 & "_" & it2     'add a new sheet with that name
               s = s & IIf(Len(s) > 0, "|", "") & it1 & "_" & it2
               ActiveSheet.Range("A1") = "this is sheet " & it1 & "_" & it2
          Next
     Next

     sp = Split(s, "|")                                         'array with the new names
     Sheets(sp).PrintPreview                                    'printpreview of all new sheets
End Sub

 

[Ankit Chowdhary]

The above code gave 9 sheets with data mentioned "this is sheet XXXXX"

Sorry for being vague, will try and explain to the best of my ability.

I have data in 3 sheets namely - NSV, Volume and NSV per tonne and the combinations for each sheets are mentioned above Arivia, Retail, Total and Core, FS, Total

Hence for each sheet there is 9 combinations. If I could get the data from the sheets as well it would be great. Currently 9 blank sheets has come up. Instead 9 sheets for NSV, 9 sheets for Volume and 9 sheets NSV per tonne should come with the above combinations.

Hope i made myself clear. In case I can show the sheet over screenshare on my gmail id

ankitchowdhary63466@gmail.com

Let me know. Thanks for the help.

 

[BSALV]

Sub comb2()
     For Each sh In Array("NSV", "Volumes", "NSV per tonne")
          k = 0
          For Each it1 In Array("Arivia", "Retail", "Total")
               For Each it2 In Array("Core", "FS", "Total")
                    s0 = sh & "_" & it1 & "_" & it2
                    Sheets.Add(After:=Sheets(Sheets.Count)).Name = s0     'add a new sheet with that name (=total 27)
                    s = s & IIf(Len(s) > 0, "|", "") & s0
                    ActiveSheet.Range("A1") = "this is sheet " & s0
                    k = k + 1
                    Sheets(sh).Range("A" & k).Value = s0        'int the original 3 sheets, write the list in A1 & next
               Next
          Next
     Next

     sp = Split(s, "|")                                         'array with the new names

     Sheets(sp).Move                                            'those 27 new added sheets to a new workbook
     ActiveWorkbook.SaveAs "book_" & Format(Now, "yyyymmdd_hhmmss")     'save that new workbook as ...
     ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Data\Stal_Excell\Map1.pdf"     'export that workbook to a pdf

     Sheets(sp).PrintPreview                                    'printpreview of all new sheets
End Sub

 

[Ankit Chowdhary]

Hi BSALV,

Thanks for the quick response. With the above code, i am getting all 27 sheets in a separate excel file but the challenge is that the sheets are blank.

The respective data of each filters is not getting moved.

Can you pls help.

 

[bebo021999]

Assum your current workbook name is "Old". Below code will dupplicate each sheet 9 times, with 9 combination of cell C2 and C3
New workbook, with 1 original sheet1 plus 27 new sheets, named like this: "NPV 1-1","NPV 1-2" ..., with cell C2 and C3 display combination.

Option Explicit
Sub split()
Application.ScreenUpdating = False
Dim drop1, drop2, i&, j&, ws As Worksheet
drop1 = Array("Core", "Arivia", "Total") ' option list in cell C2
drop2 = Array("Retail", "FS", "Total") ' option list in cell C3
Workbooks.Add
    For Each ws In Workbooks("Old").Sheets 'assume workbook with data, named "Old", adjust to actual name
        For i = 0 To 2
            For j = 0 To 2
                ws.Copy after:=Sheets(Sheets.Count)
                With ActiveSheet
                    .Range("C2").Value = drop1(i)
                    .Range("C3").Value = drop2(j)
                    .Name = ws.Name & " " & i + 1 & "-" & j + 1
                End With
            Next
        Next
    Next
Application.ScreenUpdating = True
End Sub

 

[Ankit Chowdhary]

For the purpose I changed my sheet name to "Old" and then pasted the above code.

Its throwing the below error and I did Yes to all around 10-15 times and then the excel got stuck.

Anything that I am doing wrong here.

 

[bebo021999]

For the purpose I changed my sheet name to "Old" ...

I mean workbook "Old", with the three sheets as mention in OP

 

[Ankit Chowdhary]

My Bad.. I did change my workbook name to "Old" only. Wrongly wrote sheet above.

 

[Ankit Chowdhary]

Hi.. Its still not working .. I changed the workbook to "Old" but the excel is frozen and need to restart it.