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.

 

[bebo021999]

or try with actual name?

For Each ws In Workbooks("Old").Sheets 'assume workbook with data, named "Old", adjust to actual name

 

[Ankit Chowdhary]

Its not working. Excel is getting stuck.

 

[Ankit Chowdhary]

Its still not working despite keeping the workbook name as old.. Excel is stuck and same error as above keeps on coming. Pls help

 

[Ankit Chowdhary]

Hi Team,

The below vba code is giving me new excel workbook with different 27 sheets but all the sheets are blank with no data in it. How should i modify it to give me the respective combination data also with it. Request your help guys.

 

[Ankit Chowdhary]

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

View attachment 61830View attachment 61831

Help here pls.

 

[bebo021999]

So strange!
I did it as my attached image in my 1st post.
Do the three original sheet have droppdown in C2 and C3?
If it does not work, try again to update new image showing involving range.

 

[Ankit Chowdhary]

Y

So strange!
I did it as my attached image in my 1st post.
Do the three original sheet have droppdown in C2 and C3?
If it does not work, try again to update new image showing involving range.

Yes, all three sheets have the same range in C2 and C3. Same pop up is coming once running the macro. All three sheets images attached below

 

[bebo021999]

How about new establish sheets?
For instant, "NPV 1-1"
How does it appear?

 

[Ankit Chowdhary]

How about new establish sheets?
For instant, "NPV 1-1"
How does it appear?

Its not opening only. WIth the above code as suggested by you the excel is crashing.

 

[Ankit Chowdhary]

Its not opening only. WIth the above code as suggested by you the excel is crashing.

Hi.. Kindly help me here.. Kind of urgent for me, since it is taking a lot of time