I've uploaded a workbook that should give you some ideas of how to do this.
My email address is in it also in case things aren't quite clear.

Download the .xls file, open it and click [Alt]+[F11] to view the code.
I've disabled the Print function in this workbook so you can play with it
without wasting reams of paper.

Link to the file:
http://www.jlathamsite.com/uploads/for_AB3.xls


"AB3" wrote:

Thanks very much for your reply.

Apologies if my initial post wasn't clear - hard to know how much detail to
give at first!

As it stands, the spreadsheet has a Main Data tab, a Table Template tab, and
30 tabs (created from Table Template) with individualised data for each
Department. The Main Data tab is only used as a source of data to populate
the 'Department' tabs (i.e. Main Data doesn't change and is not printed).

The Table Template tab has 1 table, and 1 drop down (created from
DataValidationList) above it - the table consists of cells with sumproduct
formulae that look to the drop-down value and return sums of corresponding
values from the Main Data tab. (Initially I thought of producing pivot tables
to show this data, but my boss hates them and has forbidden me!)

Hope this is all making sense.

The name of each tab (of the ones already created) is the same as the
Department name chosen from the drop down list. Initially, I was asked to
create tabs for 30 Departments which I didn't mind doing manually, but now
they want to print the tables for all 80 - so a macro would come in very
handy!

Would the code you've already posted still be applicable/easily modified?

Kind regards,

AB3

"JLatham" wrote:

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