Print two different areas on a worksheet

[bigjim]

I'm using excel 2003 and want to print two different areas on a sheet. Here
is the code I'm using, but when I run it, it locks up sending an error report
to MS.

ActiveSheet.PageSetup.PrintArea = "$a$1:$l$64"


ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

ActiveSheet.PageSetup.PrintArea = ""


ActiveSheet.PageSetup.PrintArea = "$a$65:$l$127"

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

 

[shg]

Works fine for me. You don't need the third line.

 

[bigjim]

It works for me if I only do the first print. When I put in the second
print, it locks up. MS sends me an error report and shuts down excel. I'll
try leaving the line out and see if that works.

 

[Barb Reinhardt]

I think I'd try something like this

Dim myRange As Range
Dim aWS As Worksheet

Set aWS = ActiveSheet
Set myRange = aWS.Range("$a$1:$l$64")
Debug.Print myRange.Address
myRange.PrintOut copies:=1, Collate:=True

Set myRange = aWS.Range("$a$651:$l$127")
myRange.PrintOut copies:=1, Collate:=True

I can't test it because I don't have a printer set up here.

 

[Gary Keramidas]

i've just adapted a report i've used in the past. try it, just change the
references to the worksheet, margins, headers and titlerows


Option Explicit
Sub Print_Report()
Dim ws As Worksheet
Dim rng As Range
Dim lastrow As Long
Dim prnArray As Variant
Dim i As Long
Application.ScreenUpdating = False
Set ws = Worksheets("Report")
ws.Unprotect
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
prnArray = Array("a1:L64", "a65:L127")

For i = LBound(prnArray) To UBound(prnArray)
Set rng = ws.Range(prnArray(i))
With ws.PageSetup
.Orientation = xlLandscape
.PrintTitleRows = "$" & 3 & ":" & "$" & 4
.CenterHorizontally = True
.CenterVertically = False
.FooterMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0.35)
.LeftMargin = Application.InchesToPoints(0.35)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.4)
.FooterMargin = Application.InchesToPoints(0#)
.PrintArea = rng.Address
.HeaderMargin = Application.InchesToPoints(0.25)
.RightHeader = "&B&12 " & ws.Range("B2").Value
.CenterHeader = "&B&16" & "Production Tracking"
.RightFooter = "Page " & "&P" & " of " & "&N" & " " & _
Format(Now, "h:mmAM/PM MM/dd/yy")
.CenterFooter = ""
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 10
.PrintGridlines = True
End With
ws.PrintPreview
Next
'Clear Print area
With ws.PageSetup
.PrintArea = ""
End With
Application.ScreenUpdating = True
ws.Protect
End Sub

 

[ShaneDevenshire]

Hi,

And this are just modifications to your code, but it already worked for me
without changes.

With ActiveSheet.PageSetup
.PrintArea = "$a$1:$l$64"
ActiveWindow.SelectedSheets.PrintOut
.PrintArea = "$a$65:$l$127"
ActiveWindow.SelectedSheets.PrintOut
End With

One copy is the default, and with one copy collate is irrelevant