How to print counted rows in excel?

October 10, 2010 at 14:42:54
Specs: Windows XP
I have an exel spreadsheet that I want to print the same header (that is more than one row) and footer (same thing that is more than one row) on every page. I know how to set the header but unfortunately in Excel you can't set the footer, same as the header.

So I put a macro together (I am a beginner in macros).

I want the macro to count the rows, print the rows with the header and footer.
The reason I am counting the rows - if the worksheet becomes more than on page I would like it to automatically to go to the next page without manually insert a page break.

The macro will print the header, count for example 15 rows, print these 15 rows, and then print the footer.

This will repeat same thing on every page if it becomes more than one page.

Here is what I have:


Private Sub CommandButton1_Click()

Dim mycount As Integer
Dim countrows As Integer
Dim sht As Worksheet

mycount = Selection.Rows.Count
mycount = 0

For mycount = 1 To 15

If mycount = 15 Then

sht.Selection("mycount").PrintOut Copies:=1, Collate:=True
sht.Range("Footer").PrintOut Copies:=1, Collate:=True

End If

Next mycount

End Sub

Thank you,

IS


See More: How to print counted rows in excel?

Report •

#1
October 10, 2010 at 15:50:14
Are you able to give us an idea on the maximum number of pages you think might ever be printed out?

I have an idea that might work for 2 or 3 pages, but I don't want to suggest it until I have a better idea of how long the document could grow to.


Report •

#2
October 10, 2010 at 15:58:14
In most cases no more than 2 pages.

Thank you


Report •

#3
October 10, 2010 at 17:41:35
The following code is a Workbook_BeforePrint event macro and must be placed in the ThisWorkbook module of the VBA editor. It will run automatically whenever you click Print within the workbook. It could be changed to be a "regular" macro and attached to a Command button if you want.

The way this code works is as follows:

1 - Create a new sheet for every 15 rows of data, plus one for any "leftover" rows, if required.
2 - Copy the Header and Footer ranges to the new sheet, then copy 15 rows of data (or part thereof)
3 - Print out the new sheets
4 - Delete the new sheets.

As written, the code requires the following set up, which you can change to fit your needs - as long as whatever changes you make to the workbook set up are properly reflected in the VBA code.

The data rows reside in Sheet1.
The data starts in Row 1.

Your Header and Footer must be named ranges and placed in a different sheet, i.e. not in Sheet 1.

In this example, the Header is 4 rows long and will be copied to Rows 1 - 4 of each new sheet.

The data will be copied to each new sheet starting in Row 5, just below the Header.

The Footer will be copied to Row 20, just below the last piece of data.

Adjust as required.

Important Note: This code Disables Events and Alerts while it is running. It Enables them again after a successful completion. However, if the code fails before it enables Events and Alerts, they will remain disabled. If you need to enable them yourself (such as if the code fails or you stop it while you are testing/modifying it) just run this simple macro to enable them again:

Sub FixEventAndAlerts
 'Reenable Alerts And Events
   Application.DisplayAlerts = True
   Application.EnableEvents = True
End Sub

I highly suggest that you try this in a backup copy of your workbook since macros cannot be easliy undone. This code deletes sheets without warning, so you do not want to be playing around with it in the only copy of your real workbook.

Here you go...

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim prtRows, numSets, numShts As Integer
Dim startRow, endRow, prtShts As Integer
'Disable Events so the printouts don't cause this
'code to fire every time it prints a sheet.
 Application.EnableEvents = False
'How many Rows of data do we need to print?
  prtRows = Sheets(1).Range("A65536").End(xlUp).Row
'How many full sets of 15 rows do we have?
  numSets = Int(prtRows / 15)
'If there are any leftover rows, add 1 to numSets
  If prtRows Mod 15 > 0 Then numSets = numSets + 1
'How many sheets are we starting with?
  numShts = Sheets.Count
'Initialize variables for Start and End Row of Data
  startRow = 1
  endRow = 15
''Loop through the sets
   For prtShts = 1 To numSets
'Add a new sheet
    Sheets.Add after:=Sheets(Sheets.Count)
'Copy Header to Row 1 (4 rows in this example)
     Range("Header").Copy _
        Destination:=ActiveSheet.Range("A1")
'Copy Footer to Row 20 (4 header rows plus 15 data rows = 20)
      Range("Footer").Copy _
        Destination:=ActiveSheet.Range("A20")
'Copy 15 rows of data to new sheet, right after header
      Sheets(1).Rows(startRow & ":" & endRow).Copy _
        Destination:=ActiveSheet.Range("A5")
'Set new Start and End Row of Data
       startRow = endRow + 1
       endRow = endRow + 15
'Loop
   Next
'Print out new sheets only
   For prtShts = numShts + 1 To Sheets.Count
     Sheets(prtShts).PrintOut
   Next
'Delete new sheets, don't ask if it's OK
'Sheets must be deleted in reverse order
'so the count doesn't get messed up
   Application.DisplayAlerts = False
     For prtShts = Sheets.Count To numShts + 1 Step -1
       Sheets(prtShts).Delete
     Next
'Reenable Alerts And Events
   Application.DisplayAlerts = True
   Application.EnableEvents = True
'Cancel the requested printout since we have already printed
   Cancel = True
End Sub



Report •

Related Solutions

#4
October 10, 2010 at 18:35:24
I am getting error that I don't know how to fix - since I am so new to macros.
Unfortunately, I can't post a picture of the error, so I am going to do my best to describe it.

The first thing it gave me an Compile error:
Variable not defined

I click on OK

It identify that there is an issue with the last line "Cancel = True"
So I made it into a comment by insterting ' at the beginning of the line

I run the macro again and it gave me:

Run-Time error '1004':
Method "Range' of object'_Worksheet failed

I clicked on debug and it highlighted:
Range("Header").Copy _
Destination:=ActiveSheet.Range("A1")

Help

Thank you,


Report •

#5
October 10, 2010 at 19:55:19
Did you set this up as a Workbook_BeforePrint macro or as a macro attached to a command button?

If you left it as a BeforePrint event macro, you should not get a "Variable not defined" error with Cancel since it is not a variable. It is a Boolean operator that tells Excel to cancel the Print operation since the code itself already took care of the printing.

A BeforePrint event macro is a macro that runs before Excel prints the sheet. If there is code within the macro to do the printing, then you need to Cancel the Excel printing operation or Excel will print whatever it thinks it's supposed to print as soon as the macro has executed all of it's instructions.

If you turned this into a "regular" macro attached to a Command button, then delete the Cancel = True line because it it not needed and will cause problems as you have found. You should also delete the Application.DisableEvents = True and Application.DisableEvents = False lines because they are not needed in a "regular" macro either.

As far as the "Method "Range' of object'_Worksheet failed" error on the Header line, I am assuming that you didn't follow this part of the set-up instructions:

Your Header and Footer must be named ranges and placed in a different sheet, i.e. not in Sheet 1.

Let me explain why this is necessary:

When you print a "long" worksheet in the normal fashion, you can tell Excel to repeat a specified number of rows at the top of each sheet. This works because Excel knows where it is going to insert a page break and knows to insert the specified rows at the top of every page.

However, in the case of this macro, we are not using that feature because we (actually, the macro) are telling Excel not only to create separate sheets but also to print some rows at the top and bottom of each sheet. In other words, the code not printing one long sheet that Excel is splitting into pages, it is creating a series of individual single page sheets and printing each one, one at a time.

Therefore, you need to turn off the "rows to repeat" feature and create your own "Header" and "Footer" as named ranges for the code to copy to each sheet as it creates it.

I think part of the confusion is the fact that we are using "Header" and "Footer" as the name of the ranges. Since these terms have an actual meaning when dealing with documents, perhaps it would be better if we use myHeader and myFooter so we know that they are not really anything built into Excel, but simply rows of text that you created and that the macro is copying to each sheet before it prints it.

Try this:

Let's say your data is on Sheet 1.

On Sheet 2, enter your "header" text in some rows and name the range "myHeader".

Also on Sheet 2, enter your "footer" text in some rows and name the range "myFooter".

In the macro, change:

Range("Header").Copy _
        Destination:=ActiveSheet.Range("A1")

to be

Range("myHeader").Copy _
        Destination:=ActiveSheet.Range("A1")

and change

Range("Footer").Copy _
        Destination:=ActiveSheet.Range("A1")

to be

Range("myFooter").Copy _
        Destination:=ActiveSheet.Range("A20")

Since the code is creating a new sheet for each 15 rows, and printing each sheet out as an individual sheet, the code needs to copy the myHeader and myFooter rows to each new sheet, then copy 15 rows of data, then print out each sheet individually.

This is very different than one long sheet being split into pages by Excel as Excel is printing it. That is a very important concept to grasp to help you understand what the code is doing.

You will notice that I added quite a few detailed comments in the code. Read them carefully to help you understand what the code is doing. Use F8 to single step through the code and watch as it adds the new sheets and copies the myHeader, myFooter and data rows.


Report •

#6
October 10, 2010 at 20:42:42
I have added the header and the footer in sheet2 and named range myHeader and myFooter.

myHeader is from A1 to K10 which includes the column header to print on each page

myFooter is from A25 to K30

Here is the modified macro:
Private Sub CommandButton1_Click()

Dim prtRows, numSets, numShts As Integer
Dim startRow, endRow, prtShts As Integer

'How many Rows of data do we need to print?
prtRows = Sheets(1).Range("A65536").End(xlUp).Row
'How many full sets of 15 rows do we have?
numSets = Int(prtRows / 15)
'If there are any leftover rows, add 1 to numSets
If prtRows Mod 15 > 0 Then numSets = numSets + 1
'How many sheets are we starting with?
numShts = Sheets.Count
'Initialize variables for Start and End Row of Data
startRow = 11
endRow = 24
'Loop through the sets
For prtShts = 1 To numSets
'Add a new sheet
Sheets.Add after:=Sheets(Sheets.Count)
'Copy Header to Row 1 (4 rows in this example)
Range("myHeader").Copy _
Destination:=ActiveSheet.Range("A1")
'Copy Footer to Row 20 (4 header rows plus 15 data rows = 20)
Range("myFooter").Copy _
Destination:=ActiveSheet.Range("A25")
'Copy 15 rows of data to new sheet, right after header
Sheets(1).Rows(startRow & ":" & endRow).Copy _
Destination:=ActiveSheet.Range("A5")
'Set new Start and End Row of Data
startRow = endRow + 1
endRow = endRow + 15
'Loop
Next
'Print out new sheets only
For prtShts = numShts + 1 To Sheets.Count
Sheets(prtShts).PrintOut
Next
'Delete new sheets, don't ask if it's OK
'Sheets must be deleted in reverse order
'so the count doesn't get messed up
Application.DisplayAlerts = False
For prtShts = Sheets.Count To numShts + 1 Step -1
Sheets(prtShts).Delete
Next

End Sub

I started the macro by using F8 as you informed me.

It give a Run-time error '1004':

Application-defined or object-defined error

It stops at:

'Copy Header to Row 1 (4 rows in this example)
Range("myHeader").Copy _
Destination:=ActiveSheet.Range("A1")

I am a confused, do I need to delete the header rows and footer rows from sheet1?

If that is the case - it is not what I am looking for. I would like to leave them on the sheet 1 because I need to e-mail the file to others and they need to see the information on the header and type the information needed in the footer.

If I remove them, it is not going to be helpfull to others. Since they need to be aware of the setup.

I need to the spreadsheet as is but able to print with a print command button the header and footer on the page and if there are more rows added to the sheet1, the header and the footer will print on each additional sheet, most likely no more than 2 pages.

I really appreciate your help.

Thank you,



Report •

#7
October 11, 2010 at 04:58:07
When you post code to this website, please use the pre tags found above the Reply box.

When you paste code between the pre tags it retains the indenting that you see in my posts and makes the code much easier to read.

I have sent you an email address via Private Message. Please remove/change any confidential data from the file and send it to me. It's taking too much of my time to try and write this code without seeing your workbook.

Please do not share the email address with anyone else.


Report •

Ask Question