Now that I see your exchange with Joel (wasn't visible when I posted), one
solution would be to define the Print Area on the sheet to only include the
table(s) you want printed. Use Format | Page Setup and the [Sheet] tab to do
that. Then the code I provided would only be printing that area each time
through the loop.

"JLatham" wrote:

Let me see if I have this straight, and also ask a couple of questions that
may lead to a better "first try" solution.

As I understand your current operation, you would pick a department from the
list and then print the Main Data Tab, choose another department and again
print the Main Data Tab which would have been repopulated with new
information? Repeat through all 80 departments?

Question #1: where does the dropdown get its list from? Need the sheet name
the list is on and the addresses of the cells containing the departments on
it.
Question #2: what is the name of the sheet (or sheets) that you want printed
with your automatic solution?
Question #3: About your 'drop-down': is it a drop down box on a user form, a
drop down box placed directly on a worksheet, or is it a cell that uses data
validation to display the list?
Question #4: If the dropdown came from a toolbox, is it from the Controls
Toolbox or the Forms tools?

Given that I've understood what you want correctly, given the answers to
those questions, I believe a solution is easily coded. My best guess at this
point is that the code would look something like shown below. Redefine the
various sheet names as required, along with the address of the cells
containing the list of departments (if it exists) and the address of the cell
that contains the name of the department when it's chosen from the dropdown.
This code kind of assumes you're using Data Validation from a list currently.
The cell addresses I put in were just arbitrary, for example only.

Sub PrintDepartmentReports()
'change these Const values as required
Const sheetToPrintName = "Main Data"
Const sheetWithDepartmentList = "Main Data"
Const departmentListAddress = "H1:H80"
'assumes Data Validation was used to
'set up a cell to select from the list
'this would have to be changed for
'use of a dropdown box to the
'address of the 'linked cell' for the
'dropdown list
Const chosenDeptCell = "A1"
Const sheetWithChosenCell = "Main Data"

Dim departmentList As Range
Dim anyDepartment As Range

Set departmentList = _
ThisWorkbook.Worksheets(sheetWithDepartmentList). _
Range(departmentListAddress)
For Each anyDepartment In departmentList
'simulate choosing a department from the list
ThisWorkbook.Worksheets(sheetWithChosenCell). _
Range(chosenDeptCell) = anyDepartment
'print the sheet with the new information
ThisWorkbook.Worksheets(sheetToPrintName).PrintOut _
Copies:=1, Collate:=True
Next ' make another selection and print revised page
Set departmentList = Nothing
End Sub


"AB3" wrote:

Hi,

Had a look on the boards but can't find similar, hope you can help.

I admit I've never created a macro before, your patience is appreciated!

The spreadsheet consists of a Main Data Tab and 30-ish other tabs, which
consist of tables that are populated from a drop-down list (ie you choose
'Department' in the dropdown, then the table is populated from the Main Data
tab accordingly).

The Department list is 80 items long, so I don't want to create 80 tabs - is
there a way I can print the data for all the items in the drop down list from
a macro? (So I'd be printing off 80 pages without creating them.)

Hope this makes sense, all help gratefully received!

Thanks,

AB3