Print two different areas on a worksheet
 

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

Print two different areas on a worksheet
 

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


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24092

Print two different areas on a worksheet
 

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.

"bigjim" wrote:

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

Print two different areas on a worksheet
 

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.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"bigjim" wrote:

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

Print two different areas on a worksheet
 

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


--

Gary
Excel 2003


"bigjim" wrote in message
...
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

Print two different areas on a worksheet
 

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
--
Thanks,
Shane Devenshire


"bigjim" wrote:

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