Printing a selected part of worksheet

M

MB

I know it's possible to highlight a portion of a
worksheet and print just that part. That works fine, but
I have a super big spreadsheet that has to be broken into
several pieces before I can print it.

I'd like to make the process more efficient by assigning
names to various portions of the spreadsheet. Then, when
I would want to print, I could simply tell Excel to print
those predesignated portions. But how do I do that?

Any help is much appreciated.
 
M

Myrna Larson

Assuming you always want to print the same areas, select each of the "pieces"
so that you end up with a "multiple selection". Then go to the File menu,
click on Print Area, then Set Print Area.
 
M

MB

I checked the website, and I think the secret is to
create a macro which directs Excel to print various cell
ranges. Can anyone give me some guidance on how to do
this? I am very confused by VBA.

Thanks!
 
R

Ron de Bruin

Hi MB

I adapt the selection macro on that page to print
Range("A1:C1,D1:G2,D12") for example

Sub test()
Dim destrange As Range
Dim smallrng As Range
Dim newsh As Worksheet
Dim Ash As Worksheet
Set Ash = ActiveSheet
Set newsh = Worksheets.Add
Ash.Select
Set destrange = newsh.Cells(LastRow(newsh) + 1, 1)
For Each smallrng In Ash.Range("A1:C1,D1:G2,D12").Areas
smallrng.Copy
destrange.PasteSpecial xlPasteValues
Set destrange = newsh.Cells(LastRow(newsh) + 1, 1)
' If you want a empty row between each area use +2
Next smallrng
newsh.Columns.AutoFit
newsh.PrintOut
Application.DisplayAlerts = False
newsh.Delete
Application.DisplayAlerts = True
End Sub


Public Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
 
Top