VBA to print two separate ranges on one sheet of paper

[EssKayKay]

I’m not sure this is possible but what I would like to do is have a VBA routine that prints two separate ranges from two different sheets in my workbook on one sheet of paper. The sheet names are Amortize and Property.

I have the two portions printing separately (Loan & Property). However, I would like the output to be limited to one page. Currently my routines print on separate sheets of paper. Here is a snipit of the code that prints the portions independently.

Loan –

Sub PrintLoanInfo()
   Application.ScreenUpdating = False

   ActiveSheet.Shapes("Straight Connector 9").Visible = True
   ActiveSheet.PageSetup.Orientation = xlPortrait
   ActiveSheet.PageSetup.LeftMargin = Application.InchesToPoints(0)
   ActiveSheet.PageSetup.RightMargin = Application.InchesToPoints(0.5)

   ActiveSheet.PageSetup.PrintArea = "$A$1:$L$27"
   ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
   ActiveSheet.PageSetup.PrintArea = ""
   Range("C2").Select

   ActiveSheet.Shapes("Straight Connector 9").Visible = False
   ActiveSheet.PageSetup.LeftMargin = Application.InchesToPoints(0.5)
   ActiveSheet.PageSetup.RightMargin = Application.InchesToPoints(0.5)
   ActiveSheet.PageSetup.PrintArea = ""
   Application.ScreenUpdating = True

End Sub

Property –

Sub PrintPropertyInfo()
   Sheets("Property").Visible = True
   Sheets("Property").Select
   ActiveSheet.PageSetup.PrintArea = "$A$2:$B$17"
   ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
   Sheets("Property").Select
   ActiveWindow.SelectedSheets.Visible = False

End Sub

Here are results of the two routines:

Loan –

Property –

Here is an example of something I would like:

Thank you for viewing,
Steve K.

 

[sxhall]

A cheat way, if there is one (!) would be to add both ranges as linked images on to a new sheet and print this instead of the two seperate ranges/sheets.

• copy the range
• go to new sheet
• right click select 'Paste special'
• select 'Linked image'
• repeat for second range

Add in any headings, think there was a line you add in the code above, set footers etc. update the print area for the sheet and set the macro to print this sheet.

The linked images update as and when any changes are made to the copied ranges with no user action required.

 

[EssKayKay]

Thank you sxhall for your response. I'm trying what you suggested but I'm having some issues with copying command buttons and receiving some memory errors. I am going to try copying all the cell formatting column widths etc. then copy all the data with a paste special values. I do not see the "Linked Image" option but maybe I just missed it. I am running an old version of Excel (v2007). I'm going to keep working on this and see what happens. I think another issue that's going to arise is the column widths between the two ranges. I'll keep you posted.

Thanks again,
Steve

 

[EssKayKay]

I think I got this working. I ran into another problem but hopefully it has nothing to do with my printing routine – seems to be completely disconnected. If needed, I will be back but for now I will consider this problem resolved.

Again, SX I thank you for your suggestion – much appreciated,

Steve