copy and printing ranges dynamically with a macro

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,879
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,879
Office Version
  1. 2016
Platform
  1. Windows
Yes I want to print from the data sheet.

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

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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.
Code:
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,879
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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.

Code:
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
 
Last edited:

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

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

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,879
Office Version
  1. 2016
Platform
  1. Windows
I get the error message "run-time error 424.
Object.Picture required

Sorry error fixed

But still working on the zoom
 
Last edited:

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,879
Office Version
  1. 2016
Platform
  1. Windows
How will I prevent the error when I click cancel?
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Which code are you using? I assume the code with the following lines.
Add the marked lines.

Code:
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

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

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,959
Messages
5,599,056
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top