Macro button to print sheets populated from drop-down list

A

AB3

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
 
J

Joel

The answer is YES. The macro would behave as if you selected 80 areas on
your worksheet and the went to the File - Print - Selection.

Now the trick is how do you define 80 areas on your worksheet. The question
becomes are the location and the size of the tables always the same or do
they change? The answer to this question determines the best way of creating
the macro. If the location of the tables are always the same and the size
changes then the macro can determine the sizes of each table by looking at
the column and row headers. You can define the areas of each of the tables in
a macro.

Another method is to use define Ranges for the tables. Create 80 names on
your worksheet corresponding to each of the tables. Use worksheet menu
Insert - Names - Define. This is nice because you could get each of the
define manes and print the areas defined by the names. Also the ranges of
the names will automatically change if you add Rows Or Columns in the middle
of the ranges.
 
A

AB3

Hi Joel - many thanks for your reply.

The tables are always the same size and in the same location. I've defined a
named range, as suggested - how do I go about creating the macro?

Much appreciated,

AB3
 
J

JLatham

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
 
J

JLatham

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.
 
A

AB3

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
Data>Validation>List) 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 said:
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 said:
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
 
J

JLatham

I believe the code posted could be easily modified.

As I understand it now, you have 30 tabs that have been created, and now you
need 50 more? And it is that group of 30 tabs you need printed one at a time?

OR is the only sheet you need to print that Table Template sheet once a new
choice has been created by making a selection in the Data Validated drop-down
list cell?

I seem a bit dense today - just having a hard time figuring out which sheets
you want to print, and what changes to determine which to print.

If you just need to print that Table Template tab over and over after a
change in the selection cell, then the code I wrote should work as is just by
changing the name of the sheet and the addresses involved.

Since I still don't know the sheet name(s) to be printed or where any of the
cells are at, I'll just say that I think you only need to change the two
constants with = "Main Data" to be = "Table Template"

And change the H1:H80 reference to the addresses of the cells used to
provide the list in the data validated cell. Finally change
chosenDeptCell = "A1"
to have the reference to the data validated/drop-down list cell. To show
these changes, I'm going to assume that your list is now in B1:B80 and that
the cell that's set up with data validation is at B81, and everything is on
the Table Template sheet.

Sub PrintDepartmentReports()
'change these Const values as required
Const sheetToPrintName = "Table Template"
Const sheetWithDepartmentList = "Table Template"
Const departmentListAddress = "B1:B80"
'assumes Data Validation was used to
'set up a cell to select from the list
Const chosenDeptCell = "B81"
Const sheetWithChosenCell = "Table Template"

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 said:
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
Data>Validation>List) 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 said:
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 said:
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


:

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
 
J

JLatham

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 said:
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
Data>Validation>List) 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 said:
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 said:
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


:

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top