Help with Excel Macro

taffylegs

New Member
Joined
Jul 21, 2007
Messages
2
have a macro in Excel that works with the exception of one piece. That is, after Print Preview and selecting all the settings I need for the header and footer, I click the close button on Print Preview. When I run the macro, it does everything just fine except at the end. It leaves the print preview of the document on screen. What can I do to get the proper code in the macro so that the preview will close and therefore be back at the worksheet after the entire macro process?
Thank you
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Can you post the code you have?
 
Upvote 0
Here's the code.

Sub TestHelp()

End Sub
Sub TestMacro()
'
' TestMacro Macro
' Macro recorded 7/21/2007 by Leslie Flaherty
'
' Keyboard Shortcut: Ctrl+d
'
Range("A2:A26").Select
Selection.NumberFormat = "m/d/yyyy"
Range("B2:B26").Select
Selection.NumberFormat = "h:mm;@"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=5/9*(RC[-1]-32)"
Selection.AutoFill Destination:=Range("D2:D26"), Type:=xlFillDefault
Range("D2:D26").Select
Selection.NumberFormat = "0.0"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Celsius"
Range("A1:D1").Select
Selection.Font.Bold = True
Columns("C:C").EntireColumn.AutoFit
Range("D27").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$26"), , xlYes).Name = _
"List1"
Range("A1:D27").Select
ActiveSheet.ListObjects("List1").ShowTotals = True
Range("D28").Select
ActiveSheet.ListObjects("List1").ListColumns("Celsius").TotalsCalculation = _
xlTotalsCalculationAverage
Range("F1").Select
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&A"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "testfile"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub
 
Upvote 0
Hmmm good question! I cant figure it out myself perhaps one of the excel gurus may know, is there a reason why you need to end on pront preview? Can you not just remove that line of code if your going to open it and then close it straight away anyway?

I thought it might be something like ClosePrintPreview but seems i am way of the mark.....Experts?
 
Upvote 0
I'm not sure that you can cancel the PrintPreview screen programatically.

In any case your code could do with a cleanup - all that selecting is unnecessary.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top