Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

Excel – printing 2 ranges on 1 page

Locked

In Lotus you could designate multiple ranges to be printed (with a comma between each)and they would print one after the other on the same page.
Does anyone have a way to do that in Excel?
When I designate mutliple print ranges, Excel prints each range on a new page.

Topic

Reply To: Excel – printing 2 ranges on 1 page

In reply to Excel – printing 2 ranges on 1 page

Here?s a macro from exceltip.com that will help you print multiple ranges on one sheet: http://www.exceltip.com/show_tip/Printing_in_VBA/Print_multiple_selections_on_one_sheet_using_VBA_in_Microsoft_Excel/456.html (remove any spaces that snuck in the url)

Sub PrintSelectedCells()
‘ prints selected cells, use from a toolbar button or a menu
Dim aCount As Integer, cCount As Integer, rCount As Integer
Dim i As Integer, j As Long, aRange As String
Dim rHeight() As Single, cWidth() As Single
Dim AWB As Workbook, NWB As Workbook
If UCase(TypeName(ActiveSheet)) <> “WORKSHEET” Then Exit Sub
‘ useful only in worksheets
aCount = Selection.Areas.Count
If aCount = 0 Then Exit Sub ‘ no cells selected
cCount = Selection.Areas(1).Cells.Count
If aCount > 1 Then ‘ multiple areas selected
Application.ScreenUpdating = False
Application.StatusBar = “Printing ” & aCount & ” selected areas…”
Set AWB = ActiveWorkbook
rCount = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
cCount = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
ReDim rHeight(rCount)
ReDim cWidth(cCount)
For i = 1 To rCount
‘ find the row height of every row in the selection
rHeight(i) = Rows(i).RowHeight
Next i
For i = 1 To cCount
‘ find the column width of every column in the selection
cWidth(i) = Columns(i).ColumnWidth
Next i
Set NWB = Workbooks.Add ‘ create a new workbook
For i = 1 To rCount ‘ set row heights
Rows(i).RowHeight = rHeight(i)
Next i
For i = 1 To cCount ‘ set column widths
Columns(i).ColumnWidth = cWidth(i)
Next i
For i = 1 To aCount
AWB.Activate
aRange = Selection.Areas(i).Address
‘ the range address
Range(aRange).Copy ‘ copying the range
NWB.Activate
Wit

Reply To: Excel – printing 2 ranges on 1 page

In reply to Reply To: Excel – printing 2 ranges on 1 page

Code got cut off – here the continuation from Wit

With Range(aRange) ‘ pastes values and formats
.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
Next i
NWB.PrintOut
NWB.Close False ‘ close the temporary workbook without saving
Application.StatusBar = False
AWB.Activate
Set AWB = Nothing
Set NWB = Nothing
Else
If cCount < 10 Then ' less than 10 cells selected If MsgBox("Are you sure you want to print " & _ cCount & " selected cells ?", _ vbQuestion + vbYesNo, "Print celected cells") = vbNo Then Exit Sub End If Selection.PrintOut End If End Sub

[Author]

Replies