Hi gmartinsen, Ken, Rob,
Well like I said, I was able to quickly create a working model based on your need for a “legend” on each printed page. The solution is reasonably simple, involving two main tasks:
A) Set up an “Update Report” button for users to click when they wish to print.
B) Use Excel’s “Camera” to take a picture of the “legend”.
Please NOTE: Some of you readers will of course be familiar with some (or perhaps most or all) of the “detail” below. Please appreciate, though, that I’m providing the detail for those who would otherwise encounter difficulty in fully understanding how to accomplish this “not so common” task.
====================================
Steps for A) Setting up the “Update Report” button.
====================================
The objective is to set up your main data sheet (let’s name this sheet “Database”) so that the data can be extracted to a separate sheet (let’s name this sheet “Report”) and printed together with a picture of the legend on each printed page – all at the “click of the button”.
1) On the Database sheet, insert a row above your data and enter a unique label for each column of your data – A to H. Also add labels (e.g. “Code and “Description”) for the columns reserved for the “legend” - columns I and J. Perhaps you already had column headings, but if not, it’s important to insert them because to be able to extract data, each column (or field) MUST have a unique “field name”.
2) Insert a sheet, and name it “Report”. The following instructions relate to setting up this separate sheet with all the report settings that will be unique to this specific report.
3) On row 1, you’ll possibly want to include a unique “Report Title”. A report title could be placed into a “Custom Header” in the “Page Setup” window. However, there can be more flexibility in setting up a Report Title on the first row of the sheet.
4) Based on Row 1 having a Report Title, copy the field names from the Database sheet to the 2nd row of the Report sheet.
5) Next you need to assign a Range Name to the field names you just copied to the Report sheet. Use these steps:
a) Highlight A2:I2.
b) Hold down the “Ctrl” key, and hit the <F3> key. This will open the “Define Name” window.
c) Type the name: “ext” (don’t enter the quotation marks used in any of the instructions)
d) Hit <Enter>.
Because you first highlighted the range, the name “ext” is assigned to the highlighted range.
6) NOTE: For now, for this example, it’s important to assign the names I’ve used, and also name the sheets the same as I have – because the VBA code (further below) refers to these names.
7) To be able to extract data, the VBA code needs to refer to what is called “Criteria”. The criteria can be as simple or as complex as is required in order to extract whatever records are required for any required report. In your particular case, as I understand it, you’ll want to extract ALL the records, but there STILL needs to be a criteria.
8) Before entering the criteria, I’d like you to insert another sheet, and name it “Criteria”. Please “trust me”. It’s VERY important to have a SEPARATE sheet for setting up the criteria that will be used for extracting data, or other database uses such as for database formulas.
9) On the sheet you’ve named “Criteria”, reserve column A for labels that you’ll enter. These labels will be for you to refer to – to know the names of the range names you’ll be assigning to the cells to the right of the labels.
9a) Reserve the first couple of rows at the top - for entering your own notes regarding the use of this Criteria sheet.
9b) In A3, enter the label: “crit”. (remember don’t enter the quotation marks).
9c) In B3, copy the field name from the first column of your “Database” sheet.
9d) In B4, enter this: <>"" In this case, I want you to enter the quotation marks. This criteria means NOT BLANK. The <> means NOT, and the two quotation marks mean BLANK.
9e) Assign the range name “crit”. Highlight B3:B4. Hold down “Ctrl”, and hit <F3>. Type the name “crit”, and hit <Enter>.
10) Notes for future use in creating criteria…
10a) When ALL records are extracted, an option is to leave the criteria “blank” – i.e. cell B4 would be left BLANK. This means an “open door” that will allow ALL records. However, a slightly different option, is to require that all records extracted MUST have a value in a particular column, as in the above example. This is useful when there can be some records in your database that you DON’T WANT because they are BLANK in a key field – i.e. they don’t have the required data to qualify for inclusion in your report.
10b) As already mentioned, the criteria can be made as complex as you need, for various reporting requirements. You can use additional field names. By adding more field names, your criteria becomes and “AND” requirement – meaning that for records to meet this criteria, they must ALSO meet whatever specification you enter under the second field name, or third field name, etc. For example, if you entered >= 200 under a second field name, it would mean records would only be included if they are NOT BLANK in the first field and they contain a value in the second field that is greater than or equal to 200.
10c) You can also include extra ROW(S) in your criteria. This becomes an OR condition.
10d) An option is use formula(s) for your criteria. It can be an advantage to use formula(s) – because it’s possible to create rather complex formulas that would be difficult to create by the “label” means described above. For example, you could use the FIND function if you wanted to include records that contained a specific word or words within a sentence under one of the fields.
10e) IMPORTANT: IF you use a formula in the criteria, it’s almost always REQUIRED that you do NOT include a field name above the cell containing the formula. Instead, keep the cell BLANK, and reference the field name within the formula. You can also reference multiple field names within ONE formula if you desire, or you can have SEPARATE formulas that reference different field names.
10f) IMPORTANT: Update the Range Name. If adding additional columns or rows, make sure to adjust the range name of your criteria. (This is where you’ll find the label in column A to be a useful reference.)
11) Steps for copying the VBA code into a Module and attaching it to a BUTTON that the user will “click” to PRINT, …or optionally to extract the data and set the print range, but to stop-short of printing. This option allows the user the option of VIEWING the report prior to printing.
11a) Hold down the <Alt> key and hit <F11>. This will bring up the “VBA Editor”. On the left (under “Project – VBA Project), make sure you’re clicked on the file you’re working on (in case there’s more than one file open). Then from the VBA menu, choose: Insert – Module. Then on the right-side, is where you need to Copy and Paste the following VBA code.
Code:
Sub Update_Report()
'Assigned to the macro button named "Update Report".
Application.ScreenUpdating = False
Extract_Data
Set_ReportData 'see NOTE below re option to automatically PRINT.
[a1].Select
Application.ScreenUpdating = True
End Sub
Sub Extract_Data()
'Extracts all data to separate sheet named "Report"
Set_Data
Range("data").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="crit", _
CopyToRange:=Range("ext"), _
Unique:=False
Worksheets("Report").Select
[a1].Select
End Sub
Sub Set_ReportData()
'Resets the range name "rept" for the number of records
'extracted to the Report sheet.
Worksheets("Report").Select
[ext].Select
ActiveCell.Offset(1, 0).Select
FirstCell = ActiveCell.Address
LastCell = [A65536].End(xlUp).Offset(0, 8).Address
rng = FirstCell & ":" & LastCell
Range(rng).Name = "rept"
ActiveSheet.PageSetup.PrintArea = "rept"
'NOTE: The user only has to use <Ctrl> P <Enter>.
'This gives the user the option to VIEW the report
'before printing.
'OPTION: IF you want the report to be automatically sent
'to the printer, remove the ' before "ActiveWindow" below.
'(& probably change the name of the button to "Print Report")
'ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub
Sub Set_Data()
'Resets the range name "data" for the number of records
'in the Database sheet.
Worksheets("Database").Select
'a1 below is based on your field names being on Row 1.
'If NOT, change a1 to the row containing your fields.
'- i.e. to a2 if your fields are on Row 2.
[a1].Select
FirstCell = ActiveCell.Address
LastCell = [A65536].End(xlUp).Offset(0, 8).Address
'In the above line, 8 means moving 8 columns from A, or
'to Column I. If the number of columns in your database
'changes, adjust the 8 to the required number.
rng = FirstCell & ":" & LastCell
Range(rng).Name = "data"
End Sub
11b) Hold down <Alt> and hit Q. This will cause you to Exit from VBA Editor.
12) Steps to Create a (Macro) Button...
There are two types of buttons… one called a “Command Button” – located on the “Control Toolbox” toolbar, and the other is called a “Button” (what you see when you hold your mouse over the icon). This button is what is sometimes referred to as a “Macro” Button. It’s located on the “Forms” toolbar, and is the one I want you to use.
12a) If you don’t have the “Forms” toolbar active… Right-click on an existing toolbar, and select “Customize” (at the very bottom of the pulldown). In the “Customize” window, under the “Toolbars” tab, put a check beside “Forms”, and close the window.
12b) Hold your cursor over the icon that looks like a button, and a yellow box will appear, saying “Button”. Click this button. Move your mouse-pointer over cell K2, and click-and-drag to M4 (approximately).
12c) When you release from the click-and-drag above, the “Assign Macro” window will appear. In the bottom-centre, opposite “Macros in:”, it should say “This Workbook” (If not, change it).
12d) Double-click on “Update_Report” (This is one of the VBA routines you copied into Module1)
12e) Now Right-click on the macro button, then click on the button text, and replace it with “Update Report”.
12f) Finally, click on the outside (border) of the button, and then change the Font to Bold, 14 point, and Blue color (for example – it’s your choice). You can also adjust the size of the button by clicking on the border (first right-click to view the border), and then click-and-drag a corner.
13) You can now “try out” your macro button. It will extract the data to the “Report” sheet and re-set the range to be printed, based on the number of records extracted.
14) If you don’t get the proper results, first re-check the above steps. Then, if you still have a problem, it might be that (if you’re really new to macros) your Macro “Security” setting has been set to “High” – which will prevent macros from running. To fix this, use (from the menu): Tools – Options – click the “Security” tab, then click the “Macro Security” button. Then under “Security Level”, choose “Medium”. Then when you open a file with macros, choose “Enable Macros” – based on either “you” having written the macros, or you have confidence in the person who did.
15) Other “Page Setup” settings for the “Report” sheet… While it’s possible to have VBA code set the other “Page Setup” settings, I have purposely NOT included these. The only setting modified by the VBA code is the “Print area”. The logic is that these other settings are normally ones for which users have a personal preference. For example… whether there should be a Report Header and its size, Page # Header and its location, Margin Settings, Rows to repeat at top (under the “Sheet” tab), etc.
16) IMPORTANT NOTE: BEFORE moving on to Section B) below, it’s crucial that you set up your Report sheet, with all of these Page Setup settings. The reason for this being so “crucial” is because the location you’ll be placing the legend “pictures” (at the top of each page) will be dependant on these settings.
====================================
Steps for B) - Using Excel’s “Camera”
====================================
The objective is to take a “picture” of the data that is to be used for the “legend”, and then copy/paste copies of this picture at the top of each page on the separate “Report” sheet. “Extra” copies of the legend will be placed on the Report sheet, to exceed the maximum number of records that could occur in the future. Because of the VBA code, the “extra” legends will NOT be printed. The only legends printed will be those opposite the number of records extracted to the Report sheet.
1) Set up of your “legend” on a SEPARATE sheet (let’s name this sheet “Legend”). Having the legend on its own sheet will allow you to make modifications easily. Also please note that any modifications you make to the legend will be “automatically” reflected in all pictures (copies) of the legend that you’ll be placing on the Report sheet.
2) To learn about setting up and using Excel’s “Camera” feature, you could check out faq68-1161 entitled “Printing Disjointed Ranges on ONE Page. But I would suggest you save this for later. For now, you only need to know that this “hidden feature” becomes “visible” (and active) when you either:
a) hold down the <Shift> key while you click “Edit” on the menu,
b) hold down <Alt> and <Shift> while you hit the letter “E”.
Then, on the Edit dropdown list, “Copy Picture” will appear.
If you didn’t notice this before, it’s “not your eyes”; it’s because it doesn’t even show as “grayed out” when you just click “Edit”.
3) Before you can take a picture of the “legend”, however, you’ll naturally need to set up the legend, as follows…
3a) On the separate “Legend” sheet, move your legend to the same columns as the pictures of it will be used on the Report sheet – which will be under the “Code” and “Description” headings – columns H and I. Move the legend data to Row 2 of the Legend sheet, saving Row 1 for entering labels. Do NOT include the Code and Description headings. Instead, in H1, enter the label “Legend”, and in D1, enter the label “Picture” (You’ll be placing your first picture here later). And (this is important) be sure to make the column widths (of H and I) the SAME as on the Report sheet.
3b) Highlight your range containing the legend data, and format with the lines you prefer.
3c) Assign the range name “legend”. Highlight the range containing the legend data, Hold down <Ctrl> and Hit <F3>, Type the name “legend”, and Hit <Enter>.
4) To take the picture… first highlight the legend range, and then use “Copy Picture” (Hold down <Alt> and <Shift> while you hit “E”). This will open the “Copy Picture” window.
4a) Select “As shown when printed”, and under “Format”, accept the default “Picture”, and click “OK”.
4b) Right-Click on D2, and choose “Paste”.
4c) IMPORTANT… While still clicked on your picture (you can see the “handles”), click on the “Formula Bar” (formula/text-edit box immediately above the column letters), and enter: =legend, and then Hit <Enter>
5) There’s one last task to do – BEFORE you begin copying the picture to the Report sheet. Again this is IMPORTANT. You need to identify where the “Page-Breaks will fall – for a number of pages that will exceed the maximum number of records that will occur in the future.
5a) Go to the Report sheet, and change the print area. From the menu: File – Page Setup. Click on the “Sheet” tab, and change the row number. For example, change the row to 1000 (or whatever number will exceed the maximum records that could occur). Now click on “Print Preview”, and then hit “Escape”. (You do NOT want to print all these pages – not now at least).
5b) Now check to make sure you can see the Page Break lines. If you don’t see them, it will be because you need to use: Tools – Options – click the “View” tab, and put a check-mark beside “Page breaks”.
6) You’re NOW ready to begin copying this picture to the Report sheet, as follows…
6a) Click on the “Legend” tab, and then Right-Click on the Picture you already took, and choose “Copy”.
NOTE: Be aware that if you (accidentally) “double-click” on the Picture, you’ll “jump” to the “legend” (from which your picture was taken).
6b) Click on the “Report” tab, and then click on the cell immediately below the cell containing the label “Code”.
6c) Now use “Paste”. Especially when doing a lot of pasting, using the shortcut of <Ctrl> V is very fast and effective.
6d) NOTE: The picture (copy of) you’ve just pasted should normally start in the same cell as your cursor in on. However, sometimes the picture will fall 1 cell below the cell your cursor is on. If this happens, the “easy solution”, is to click on the picture and <Delete> it with the <Delete> key. Then move up 1 cell, and paste. This will place the picture in the proper location.
6e) Hit <Page-Down> until you come to each of the Page-Break lines, and simply use <Ctrl> V at each Page-Break line. Do this until you’ve reached the bottom of your “temporary” print area.
7) Don’t worry about re-setting the Print area. Just go to the Report sheet, and click on your Update Report macro button – and this will re-set the Print area.
7a) After clicking the Update Report button, you’ll see with Print-Preview that the “extra” legends will NOT print – only those legends opposite the records extracted to the Report sheet.
8) Please note that during the extraction process (where only the field names are identified as the range to extract to – in this case the range named “ext”), ALL data below the “ext” range (on the Report sheet) will be eliminated when the “new” extraction of data happens. Therefore, do NOT place any data below the “ext” range on the Report sheet that you want to keep. NOTE: This deletion of data does NOT affect the pictures (of the legend). There is the option of specifying a “range of rows” instead of just the one row. However, this opens up the possibility that the specified range could be too small to accept the number of records. Having a one row (“ext” range in this case) is therefore preferable in most cases.
WOW – I knew it was going to be long, but I never imagined it would be “quite” this LONG. I hope you and other Tek-Tips members have been able to follow along. And of course I hope members will find this information useful.
A final note: Reading the notes I’ve included within the VBA code can be useful in case you need to make modifications.
Regards, Dale Watson
![[peace]](/data/assets/smilies/peace.gif "[peace] [peace]")
It's easier to beg forgiveness than ask permission![[2thumbsup]](/data/assets/smilies/2thumbsup.gif "[2thumbsup] [2thumbsup]")