copy and printing ranges dynamically with a macro

[kelly mort]

Hello All,
I have this challenge here with me;
I am using a macro to print some work sheets and I am getting myself into a trap.

I used some sheets to lookup data from the data sheet then print them. So say I have 1000 rows of data and and I have to print 50 on a sheet, then I will insert 20 sheets to do the lookup. Then I have a macro that looks for those sheets and print them.

I did that for maximum data entry. But I may have less than 1000 sometimes. And when this happens then the macro prints only the sheets with data in them. It worked good with small data but now I think I need something better.

Any suggestions?
To avoid those number of sheets?

Can I use a macro to copy say first 50 then paste on a sheet and print then next 50 etc till the last data? Because I want all my data have headings when printed. Thanks in advance
Kelly

 

[jolivanes]

So essentially you only want to print data from the data sheet, correct?
Which row(s) is/are the header row(s)
Are these header rows the same for all sheets?
If so, have a look at Page Setup - Sheet - Rows to repeat at top.
How do you determine which pages (= parts of one sheet) to print?

 

[kelly mort]

Yes I want to print from the data sheet.

I will print everything from the data sheet where the is data

 

[jolivanes]

Try this and see if this is what you need.
It is currently set to Print Preview. Once when you're happy, change it to print.

Sub Print_All()
Dim lr As Long, lc As Long, rpp As Long
Dim xTitleId As String, Row_Index As Long
lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
rpp = Application.InputBox("Rows per page", xTitleId, "", Type:=1)
    ActiveWindow.View = xlPageBreakPreview
    ActiveWindow.View = xlNormalView
        With ActiveSheet
            .ResetAllPageBreaks
            .PageSetup.PrintArea = .Range("A1:A" & lr).Resize(, lc).Address    '<---- Column A to last used column
            .PageSetup.PrintTitleRows = "$1:$2"    '<----- First 2 Rows as headers. Change as required
                For Row_Index = rpp + 1 To lr Step rpp
                    .HPageBreaks.Add Before:=.Cells(Row_Index, 1)
                Next
        End With
ActiveSheet.PrintPreview    '<----- Change to print when you're satisfied all is OK
End Sub

 

[kelly mort]

It works but does not accept my number of rows. It sets it's own page break. How do I resolve that?

 

[jolivanes]

I don't know why it would do that. Did you make any changes to the code?
Could it be that you ask for more lines than fit on a page? Try with less lines, you'll see it when it goes to PrintPreview.
You could try this in the meantime.

Sub HPage_Breaks()
Dim rpp As Long, rh As Long, i As Long, lr As Long, lc As Long

lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
rpp = Application.InputBox("Rows per page", "Rows Each Page", "", Type:=1)
rh = Application.InputBox("How many repeating header rows?", "Header Rowse", "", Type:=1)

ActiveWindow.View = xlPageBreakPreview
ActiveWindow.View = xlNormalView

With ActiveSheet
    .ResetAllPageBreaks
        .PageSetup.PrintArea = .Range("A1:A" & lr).Resize(, lc).Address    '<---- Column A to last used column
            .PageSetup.PrintTitleRows = "$1:$" & rh    '<----- First 2 Rows as headers. Change as required
                .HPageBreaks.Add (.Cells(rpp + 1, 1))
                    For i = 1 To lr / (rpp - rh)
                        .HPageBreaks.Add (.Cells(.HPageBreaks(i).Location.Row + rpp - rh, 1))
                    Next i
End With
ActiveSheet.PrintPreview
End Sub

 

[jolivanes]

If you have to many lines and you are restricted to that amount, you can add a line to change the Zoom level, like so

With ActiveSheet
    .ResetAllPageBreaks
    .PageSetup.Zoom = 92    '<---- Add this line and experiment with the number 92

 

[kelly mort]

I get the error message "run-time error 424.
Object.Picture required

Sorry error fixed

But still working on the zoom

 

[kelly mort]

How will I prevent the error when I click cancel?

 

[jolivanes]

Which code are you using? I assume the code with the following lines.
Add the marked lines.

rpp = Application.InputBox("Rows per page", "Rows Each Page", "", Type:=1)
rh = Application.InputBox("How many repeating header rows?", "Header Rowse", "", Type:=1)
On Error GoTo Cancelled    '<---- Add this line here
ActiveWindow.View = xlPageBreakPreview

End With
ActiveSheet.PrintPreview
Cancelled:    '<---- Add this line here
Exit Sub    '<---- Add this line here

End Sub