Printing multiple named ranges on one page

  • Thread starter ryan.fitzpatrick3
  • Start date
R

ryan.fitzpatrick3

I have several named ranges. I would like to print them out on one
page instead of them printing out on separate pages. Here's my code:

Sheets("Data").Select
Range("LA").Select
Selection.PrintOut Copies:=1, Collate:=True

Sheets("Data").Select
Range("Richmond").Select
Selection.PrintOut Copies:=1, Collate:=True

Sheets("Data").Select
Range("Joplin").Select
Selection.PrintOut Copies:=1, Collate:=True

Sheets("Data").Select
Range("Calgary").Select
Selection.PrintOut Copies:=1, Collate:=True

I would like to print them out where the next named range prints out
underneath the previous one and maybe even have a row space to clean
up the sheet. Is this possible and what code should I use?

Ryan
 
R

Ron de Bruin

Test this one

Sub Test()
Dim Destrange As Range
Dim Smallrng As Range
Dim Newsh As Worksheet
Dim Ash As Worksheet
Dim Lr As Long

Application.ScreenUpdating = False

Set Ash = ActiveSheet
Set Newsh = Worksheets.Add
Ash.Select

Lr = 1

'You can also use a range with more areas like this
'For Each smallrng In Ash.Range("A1:C1,D10:G20,A30").Areas

For Each Smallrng In Selection.Areas
Smallrng.Copy
Set Destrange = Newsh.Cells(Lr, 1)
Destrange.PasteSpecial xlPasteValues
Destrange.PasteSpecial xlPasteFormats
Lr = Lr + Smallrng.Rows.Count + 1
Next Smallrng

With Newsh.PageSetup
.Orientation = xlLandscape
End With
Newsh.Columns.AutoFit

Newsh.PrintOut

Application.DisplayAlerts = False
Newsh.Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top