Going Crazy

[Guest]

Good Morning Everybody:

I have a macro that contains 12 sub macros. If I run the macro using the F8
Step Into command they all run fine. What they do is print out a chart and
the data from two different ws's in one wb. Running it with the Step Into
command it runs fine, however if I run it with the run command it works fine
for the first 7 and then for the last 5 it only prints out the chart and not
the data ws. The macro does not stop it just seems to by pass the second half
of the sub macro whereby it should bring up a print preview screen. Here is
a sample of the one that is bypassing the print preview command. Any Thoughts

Sub Print6030Chart()
Workbooks.Open Filename:= _
"C:\Documents and Settings\t_frankb\My Documents\Excel\Weekly
Stability Metrics\Weekly Stability Metrics
Reports\06182007\Charts\Weekly_Stability_Metrics_SC1x_6030_3.x_6_18_07.xls"
Sheets("Weekly Stability Metrics").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Weekly Arrival Data").Select
Application.Goto Reference:="PrintRange"
Selection.PrintOut Copies:=1, Preview:=True, Collate:=True
ActiveWindow.Close
End Sub

Thanks
Frank

 

[Susan]

Sheets("Weekly Stability Metrics").Select
Sheets("Weekly Arrival Data").Select

are these 2 sheets the only sheets in each particular workbook?
rather than selecting them & printing them individually, why don't you
try

Worksheets.PrintOut
or Sheets.PrintOut

or

Sheets("Weekly Stability Metrics").PrintOut Copies:=1, Collate:=True
Sheets("Weekly Arrival Data").PrintOut Copies:=1, Collate:=True
without selecting them.

susan

 

[Guest]

Susan thanks for getting back to me.

As you can see I am using the go to command to pick a range that is
selected. If I just use the command you suggested it would print out all 50
pages and the range is for only one page. Can I add the print range to your
suggestion>>>

Thanks
Frank

 

[Gary Keramidas]

i don't select the sheets when i use a print routine. i use something like this:

Set ws = Worksheets("Sheet1")
Set rng = ws.Range("A1:X22")

With ws.PageSetup
'.LeftHeaderPicture.Filename = ThisWorkbook.Path & "\logo.jpg"
.HeaderMargin = Application.InchesToPoints(0.25)
.LeftHeader = "&G"
.RightMargin = Application.InchesToPoints(0.4)
.LeftMargin = Application.InchesToPoints(0.4)
.TopMargin = Application.InchesToPoints(1.5)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintArea = rng.Address
.CenterHeader = "&B&16Production Header"
.RightHeader = "&B&12 " & Date
.CenterHorizontally = True
.CenterVertically = False
.Zoom = 83
End With

Select Case MsgBox("Print " & Numcop & " Copies of Production Header?",
vbYesNo Or _
vbQuestion Or vbDefaultButton1, "Print")
Case vbYes
' ws.PrintPreview
ws.PrintOut Copies:=1, Collate:=True
Case vbNo
GoTo Xit
End Select

 

[Susan]

i don't see why not.........

Sheets("Weekly Stability Metrics").PrintRange.PrintOut Copies:=1,
Collate:=True

but i didn't test it................


susan, strapping on her Acme roller skates & lighting her Acme Jet
Powered backpack to join frank in another attempt to catch that darn
road runner............. LOL

 

[Susan]

now i see why not..............
setting the print range via VBA is very difficult:
http://groups.google.com/group/micr...t+range+via+vba&rnum=1&hl=en#4f475c77985a22e0
in testing what i told you, i found that out!

this is what i finally came up with, for testing:


Sub beep_beep()

With Sheets("Weekly Stability")
.PageSetup.PrintArea = .Range("Print_Area").Address
End With

Sheets("Weekly Stability").PrintOut

End Sub


and it works. but looking at your stuff, & the problem you're having,
i can't see any other way to do it than the way you're doing it.......
not without setting a lot of variables or named ranges.
sorry
i guess my backpack blew up.
:]
susan