Can anyone give advice on how to accomplish the following:

I need a print macro in excel where I have two drop down list the
first one being "defined" as "category" & the second being "defined"
as "consunit." The category & the consunit dropdown lists are built
off of a list on a new sheet labeled "date information". What I need
is when i click on the print macro, it will go to Category & read the
first item in the drop down, then read the first item in the consunit
dropdown & print. Then I want it to stay on the first item in the
Category drop-down & pick the second item in the consunit drop-down &
print. Once it finishes scrolling through all of the consunits, it
should then go to the next item in the category list & the print each
item again in the consunit drop-down. Then the third item in the
category list & all items listed in the consunit dropdown. (take the
1st item from List A, & print this item with each of the items in the
List B, then pick the 2nd item from List A & cycle again through all
the items of List B, & so forth.

"Calibutterfly",

When your Print macro prints each "Category" and "Consunit" pair, exactly
what gets printed? A range of cells somewhere (where), an entire worksheet
(how is it named?), an external data file (name and folder?), or what? We
need more detailed info to give you any pointers.

--
Regards,
Bill Renaud

On Oct 8, 7:53 pm, "Bill Renaud"
wrote:
"Calibutterfly",

When your Print macro prints each "Category" and "Consunit" pair, exactly
what gets printed? A range of cells somewhere (where), an entire worksheet
(how is it named?), an external data file (name and folder?), or what? We
need more detailed info to give you any pointers.

--
Regards,
Bill Renaud
Hi Bill,

Thanks for the follow-up. Below you will find the exact ranges for
all of the items.
Print Area = 'SGA Comparative'!$B$1:$Q$142
Drop down list 1 (category) range = 'Date Information' D33:D59
Drop down list 2 (cons unit) range= 'Profit and Loss GC' HX10:HX59
**[the second drop down list could be less than the HX59 range
(meaning 40 items instead of 50) depending on the data to pull in. Is
there anyway to tell it to stop scrolling once it hits a blank cell?]

The two drop-down lists are on the SGA comparative tab. I have a
button created at the top that I usually link the macro to is labeled
as "Print All Worksheets"

Thanks.

You can try the following code (developed in Excel 2000). The code to refer
to your comboboxes may be different in a later version of Excel.

I am assuming that you are printing out the 'SGA Comparative' worksheet,
after each combination of category and consunit has been selected. If the
'SGA Comparative'worksheet requires 3 pages to print out each time, then
you could potentially have somewhere in the range of 4,000 pages to print
(27 categories * 50 consunits * 3 pages each)! I hope this is what you
intend!

Better comment out the line of code "wsSGAComparative.PrintOut" or change
it to "wsSGAComparative.PrintPreview", so you can check and then cancel the
printout of each page while you are testing.

'----------------------------------------------------------------------
'Use Tools|References to set a reference
'to "Microsoft Forms X.X Object Library".
Public Sub PrintAllWorksheets()
Dim wsSGAComparative As Worksheet

Dim chkCategory As Shape 'MSForms.ComboBox
Dim rngCategoryData As Range
Dim rngCategoryLinkedCell As Range

Dim chkConsUnit As Shape 'MSForms.ComboBox
Dim rngConsUnitData As Range
Dim rngConsUnitLinkedCell As Range

Dim lngCategoryCount As Long 'Actual number of category items.
Dim lngConsUnitCount As Long 'Actual number of consunit items.

Dim ilngCategory As Long 'Index to use while printing sheets.
Dim ilngConsUnit As Long 'Index to use while printing sheets.

On Error GoTo ExitSub

'Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wsSGAComparative = Worksheets("SGA Comparative")

'Get references to combo boxes.
With wsSGAComparative
Set chkCategory = .Shapes("category")
Set chkConsUnit = .Shapes("consunit")
End With

'Locate category data areas.
With chkCategory.ControlFormat
Set rngCategoryData = Application.Range(.ListFillRange)
Set rngCategoryLinkedCell = Application.Range(.LinkedCell)
lngCategoryCount = rngCategoryData _
.SpecialCells(xlCellTypeConstants) _
.Count
End With

'Locate consunit data areas.
With chkConsUnit.ControlFormat
Set rngConsUnitData = Application.Range(.ListFillRange)
Set rngConsUnitLinkedCell = Application.Range(.LinkedCell)
lngConsUnitCount = rngConsUnitData _
.SpecialCells(xlCellTypeConstants) _
.Count
End With

For ilngCategory = 1 To lngCategoryCount
'Set the combobox value to an item from the list.
rngCategoryLinkedCell.Value = ilngCategory

For ilngConsUnit = 1 To lngConsUnitCount
rngConsUnitLinkedCell.Value = ilngConsUnit

'Insure worksheet is up-to-date before printing.
Application.Calculate

'Print the worksheet with the filled-in data.
wsSGAComparative.PrintOut
Next ilngConsUnit
Next ilngCategory

ExitSub:
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

--
Regards,
Bill Renaud

Here is a more correct version, since Forms Controls on a worksheet (in
Excel 2000) are contained inside a shape object.

'----------------------------------------------------------------------
'Code for Excel 2000.
'Use Tools|References to set a reference
'to "Microsoft Forms 2.0 Object Library".

Public Sub PrintAllWorksheets()
Dim wsSGAComparative As Worksheet

Dim cboCategory As ControlFormat
Dim rngCategoryData As Range
Dim rngCategoryLinkedCell As Range

Dim cboConsUnit As ControlFormat
Dim rngConsUnitData As Range
Dim rngConsUnitLinkedCell As Range

Dim lngCategoryCount As Long 'Actual number of category items.
Dim lngConsUnitCount As Long 'Actual number of consunit items.

Dim ilngCategory As Long 'Index to use while printing sheets.
Dim ilngConsUnit As Long 'Index to use while printing sheets.

On Error GoTo ExitSub

Application.Calculation = xlCalculationManual

Set wsSGAComparative = Worksheets("SGA Comparative")

'Get references to drop-down combo boxes.
With wsSGAComparative
Set cboCategory = FormsControlOnWorksheet(wsSGAComparative, _
"category")
Set cboConsUnit = FormsControlOnWorksheet(wsSGAComparative, _
"consunit")
End With

'Locate category data areas.
With cboCategory
Set rngCategoryData = Application.Range(.ListFillRange)
Set rngCategoryLinkedCell = Application.Range(.LinkedCell)
lngCategoryCount = rngCategoryData _
.SpecialCells(xlCellTypeConstants) _
.Count
End With

'Locate consunit data areas.
With cboConsUnit
Set rngConsUnitData = Application.Range(.ListFillRange)
Set rngConsUnitLinkedCell = Application.Range(.LinkedCell)
lngConsUnitCount = rngConsUnitData _
.SpecialCells(xlCellTypeConstants) _
.Count
End With

For ilngCategory = 1 To lngCategoryCount
'Set the combobox value to an item from the list.
rngCategoryLinkedCell.Value = ilngCategory

For ilngConsUnit = 1 To lngConsUnitCount
rngConsUnitLinkedCell.Value = ilngConsUnit

'Insure worksheet is up-to-date before printing.
Application.Calculate

'Print the worksheet with the filled-in data.
'Change Preview parameter to True to preview before printing.
wsSGAComparative.PrintOut Preview:=False
Next ilngConsUnit
Next ilngCategory

ExitSub:
Application.Calculation = xlCalculationAutomatic
End Sub

'----------------------------------------------------------------------
Public Function FormsControlOnWorksheet(ws As Worksheet, _
strShapeName As String) _
As ControlFormat

Dim shp As Shape

On Error GoTo ExitFunction

'Attempt to get the shape that contains the Forms Control.
Set shp = ws.Shapes(strShapeName)

If shp.Type = msoFormControl _
Then
'Shape contains a Forms Control.
Set FormsControlOnWorksheet = shp.ControlFormat
Else
'Shape contains something other than a control.
Set FormsControlOnWorksheet = Nothing
End If

ExitFunction:
End Function


--
Regards,
Bill Renaud