Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro button to print sheets populated from drop-down list
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro button to print sheets populated from drop-down list
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. "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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro button to print sheets populated from drop-down list
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 "Joel" wrote: 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. "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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro button to print sheets populated from drop-down list
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro button to print sheets populated from drop-down list
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro button to print sheets populated from drop-down list
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro button to print sheets populated from drop-down list
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" 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro button to print sheets populated from drop-down list
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
print button macro & specific tabs to print | Excel Worksheet Functions | |||
A button on one sheet to print other sheets | Excel Worksheet Functions | |||
Drop-down list Populated by a Subset of a larger list | Excel Worksheet Functions | |||
drop down list button | Excel Discussion (Misc queries) | |||
Can a cell have a drop down list and can also be auto populated | Excel Worksheet Functions |