EXCEL - Keep 1 row and 2 columns on every print page 2

[gmartinsen]

I want to keep row 1 A - I and Columns H and I rows 1 - 36 on every page when printed. The row contains headers and the 2 columns contain fixed data I want to appear on every page I print. I want the user to enter data on each row starting at row 2 columns A - G and the row 3 Columns A - G and so on. Even when they get to row 100 for example I want the Row 1 and data in Columns H and I rows 1 - 35 to appear on that page for reference. HOW do I do this?

 

[KenWright]

That makes no sense to me. Keeping the Header rows constant, no problem, but you cannot have Row 35 visible against row 100, and it wouldn't be logical to do so anyway. What exactly are you trying to achieve? What kind of data is in Cols H & I?

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[gmartinsen]

This is actually for someone else. I am doing research.
Column C might contain MHT or PHX or IAD. they want the explnation shown on the right. MHT - Manchester NH PHX - Phoenix AZ IAD - Dulles MD. The amount of data is > 255 caracters so it cannot go into a footer. How can these code explanations be shown on every page?

 

[KenWright]

Ahhh - OK that makes a bit more sense to me now
Doesn't mean I have an answer I'm afraid, but it helps to narrow it down to what is really required. Will keep thinking.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[DaleWatson123321]

Hi gmartinsen,

Just to let you know that it's possible, I quickly created a working model. Problem is, I'm now in the process of describing what I've done - which, while fairly simple (once you know how), requires proper instructions for someone doing it for the first time.

There are a couple of options. I've chosen the one that will make it very easy to make any future updates to your "legend". I'm expecting this is NOT a one-time-only task; otherwise it likely won't be worthwhile to use this easy-update option.

I expect to post the instructions on the weekend (perhaps earlier depending on my workload this afternoon).

Regards, Dale Watson

 

[AlaskanDad]

Now, I'M looking forward to your solution as well!

I came up with one but it would only have the repeating columns on the right hand side as necessary. Not terribly elegant.

Rob

 

[DaleWatson123321]

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.

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

 

[TonyJollans]

Hi All,

Another fine piece of work, Dale! I would, however, like to make another suggestion which can't easily be totally automated but which might be a little simpler.

It seems to me that having a Legend in Row 1 and Columns H and I is a bit odd on screen or on paper so I make the same assumption as Dale that it can be moved somewhere else and, perhaps, reformatted a bit.

Although text in headers and footers is limited, you have the option to include images, so you can take a picture (using Excel's Camera as described by Dale) of the Legend and save it as a picture (to test this I just pasted it into Paint and saved it). You can then include that picture in the footer and print your sheet as normal. You will have to take care to adjust the page margins so that there is room for the picture but that should be all the tweaking needed.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[KenWright]

And just becuase I'm lazy - I'd set up the file with just the column and row headers on, no data. Print that one sheet, photocopy it and then use that paper in my printer, just printing the data without Row/Column headers / legends

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[DaleWatson123321]

Hi Tony and Ken,

Thanks for your additional suggestions/options, as they could be realistic alternatives.

Whether your options or mine is preferable could depend on:
a) whether or not the task is a one-time-only task, and
b) if it’s not, whether or not the legend will change from time-to-time, and how often.

It’s IMPORTANT to appreciate that with my option, it becomes EXTREMELY EASY to make updates to the legend – i.e. it’s just a matter of inserting a row for a new entry, or simply editing any existing entry. Because the “pictures” are linked to the legend, the updates are totally “automatic”.

It’s also IMPORTANT to note… My option took me all of about 10 to 15 minutes maximum, and it would have taken even less time if I already had the actual database.

It probably “looks” a little intimidating because of my rather detailed instructions. However, with these instructions, it will hopefully serve as a MUCH easier way of learning:
a) the use of the Camera,
b) the use of Excel’s “Advanced Filter” functionality.

But, bottom-line is, after a “newbie” or others have gone through the instructions “once”, the second-time-around will be MUCH easier and faster. And there’s NO reason why they could not duplicate my 10-15 minute timeframe to complete this task.

As I’ve mentioned in the past, I believe the “Advanced Filter” functionality of Excel is “grossly under-used”, especially given the significant power it offers. Certainly Pivot Tables are a significant option for reports, but one should still not overlook the “Advanced Filter” because it offers some areas of flexibility that Pivot Tables don’t offer.

The above will, I hope, serve as not just a solution for this rather “unique” reporting requirement, but ALSO serve as a means by which Excel users can become familiar with the “Advanced Filter” and the "Camera".

Regards, Dale Watson

 

[KenWright]

Hi Dale - detail works for me

I like to use a Noddy approach when I can as there are plenty of people that appreciate a real step by step guide, as opposed to one that assumes a certain level of knowledge.

Star from me for that one

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[DaleWatson123321]

Thanks, Ken ;-)

Much appreciated, especially coming from an MVP.

Regards, Dale

 

[KenWright]

LOL

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------