Print Preview Macro for multiple ranges

[RobdogJenny]

Hello,

This is my first query, so I hope I've provided enough information... anything I've forgotten, please let me know.

I have a sheet with many tabs, and I want to create a print macro (that previews the print, and doesn't just send it straight to the printer). All my tabs are named but I've called them Sheet1, Sheet2 etc. below for clarity.

Sheet1 (A1:G10)
Sheet2 (A1:G10)
Sheet3 (A1:H12)
Sheet5 (A1:I56)
and a print area that is called "Infrastructure_Print"

I've got as far as:

Sub PrintSomeCells()
Sheets("Sheet1").Select
Range("A1:G10").PrintPreview
Sheets("Sheet2").Select
Range("A1:G10").PrintPreview
Sheets("Sheet3").Select
Range("A1:H12").PrintPreview
Sheets("Sheet5").Select
Range("A1:I56").PrintPreview
End Sub

but I can't work out how to put the print area in at the end, and I'm getting 4 separate print previews, when I only want 1. I want to be able to click a button and have only those ranges show in Print Preview.

Is this possible?

Many thanks in advance,
Jen

 

[DanteAmor]

An option would be to copy the 4 ranges, one below the other, on a new sheet and print Preview the new sheet.

 

[RobdogJenny]

Hi,

Thanks for getting back to me

How do you define the ranges and where they would copy in VBA? (Sorry - a bit of a VBA novice - I cobbled my example together just with google and trial and error). Could you "paste this table including its formatting, but none of its named ranges, leave a couple of lines, and paste the next range underneath, and repeat? Then do the print preview thing?

The ranges can change all the time, so ideally, I don't want to be updating them in more than one place...

The macro described in the original post would be ideal because I want to be able to click a button that prints the data in several sheets presented in different ways on their own page and in the right order..

 

[DanteAmor]

Try the following to see if it helps you.
Create a sheet with the name "SheetPrint".

Sub PrintPrev()
  Dim lr As Long, lc As Long, lr2 As Long
  Dim arr As Variant, sh As Variant
  Dim sPrint As Worksheet
  
  Application.ScreenUpdating = False
  Set sPrint = Sheets("SheetPrint")
  sPrint.Cells.Clear
  
  arr = Array("Sheet1", "Sheet2", "Sheet3", "Sheet5")
  lr2 = 1
  For Each sh In arr
    With Sheets(sh)
      lr = .Range("A" & Rows.Count).End(3).Row
      lc = .Cells(1, Columns.Count).End(1).Column
      .Range("A1", .Cells(lr, lc)).Copy sPrint.Range("A" & lr2)
      lr2 = sPrint.Range("A" & Rows.Count).End(3).Row + 3
    End With
  Next
  Application.ScreenUpdating = True
  sPrint.PrintPreview
End Sub