Macros in Excel

C

Cheryl

Will a macro close print preview. I had a macro perform other tasks, and then
open in print preview, however it will not close the preview. I really need
to. What am I doing wrong. I am pasting the macro below.

--
Cheryl/

Sub ConvertData()
'
' ConvertData Macro
' Converts date, time, temperature to Celcius, bolds, averages celcius temp,
print preview
'
' Keyboard Shortcut: Ctrl+d
'
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("B:B").Select
Selection.NumberFormat = "h:mm;@"
Columns("C:C").EntireColumn.AutoFit
Range("D1").Select
ActiveCell.FormulaR1C1 = "Celcius"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("D2").Select
ActiveCell.FormulaR1C1 = "=5/9*(RC[-1]-32)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D26"), Type:=xlFillDefault
Range("D2:D26").Select
Columns("D:D").Select
Selection.NumberFormat = "0.0"
Range("A1:D1").Select
Selection.Font.Bold = True
Columns("C:C").ColumnWidth = 11
Range("A27").Select
ActiveCell.FormulaR1C1 = "Average"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("A27").Select
Selection.Font.Bold = True
Range("D27").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-25]C:R[-1]C)"
Range("D27").Select
Selection.Font.Bold = True
Range("F1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "MediTrax Controls"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "Cheryl Sarver"
.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 = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = False
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub
 
J

Joerg Mochikun

I understand that the Printpreview window is meant to let humans examine the
contents and only when they are happy with the result proceed with the
actual printing. How could this ever be entrusted to a macro? The
Printpreview window has to be closed manually and I don't think that even a
Sendkeys statement would help you since macro execution doesn't proceed
unless you close the window.

JM

Cheryl said:
Will a macro close print preview. I had a macro perform other tasks, and
then
open in print preview, however it will not close the preview. I really
need
to. What am I doing wrong. I am pasting the macro below.

--
Cheryl/

Sub ConvertData()
'
' ConvertData Macro
' Converts date, time, temperature to Celcius, bolds, averages celcius
temp,
print preview
'
' Keyboard Shortcut: Ctrl+d
'
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("B:B").Select
Selection.NumberFormat = "h:mm;@"
Columns("C:C").EntireColumn.AutoFit
Range("D1").Select
ActiveCell.FormulaR1C1 = "Celcius"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("D2").Select
ActiveCell.FormulaR1C1 = "=5/9*(RC[-1]-32)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D26"), Type:=xlFillDefault
Range("D2:D26").Select
Columns("D:D").Select
Selection.NumberFormat = "0.0"
Range("A1:D1").Select
Selection.Font.Bold = True
Columns("C:C").ColumnWidth = 11
Range("A27").Select
ActiveCell.FormulaR1C1 = "Average"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("A27").Select
Selection.Font.Bold = True
Range("D27").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-25]C:R[-1]C)"
Range("D27").Select
Selection.Font.Bold = True
Range("F1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "MediTrax Controls"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "Cheryl Sarver"
.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 = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = False
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub
Click to expand...
 
D

Don Guillett

The simple answer is change printPREVIEW to printOUT. However, you can also
clean up to remove selections.


Sub ConvertData()
'
' ConvertData Macro
' Converts date, time, temperature to Celcius, bolds, averages celcius temp,
print preview
'
' Keyboard Shortcut: Ctrl+d
'
Columns("A").NumberFormat = "m/d/yyyy"
Columns("B").NumberFormat = "h:mm;@"
Columns("C").AutoFit
Range("D1")="Celcius"
Range("D2").FormulaR1C1 = "=5/9*(RC[-1]-32)"
Range("D2").AutoFill Destination:=Range("D2:D26"), Type:=xlFillDefault
Columns("D").NumberFormat = "0.0"
Range("A1:D1").Font.Bold = True
Columns("C").ColumnWidth = 11

Range("A27")= "Average"
Range("D27").FormulaR1C1 = "=AVERAGE(R[-25]C:R[-1]C)"
Range("a27, D27").Font.Bold = True

'Page setups REALLY slow down macro execution so should be done beforehand
if possible. If not, ONLY change what is necessary. NOT everything.

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With

ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "MediTrax Controls"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "Cheryl Sarver"
.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 = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = False
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Cheryl said:
Will a macro close print preview. I had a macro perform other tasks, and
then
open in print preview, however it will not close the preview. I really
need
to. What am I doing wrong. I am pasting the macro below.

--
Cheryl/

Sub ConvertData()
'
' ConvertData Macro
' Converts date, time, temperature to Celcius, bolds, averages celcius
temp,
print preview
'
' Keyboard Shortcut: Ctrl+d
'
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("B:B").Select
Selection.NumberFormat = "h:mm;@"
Columns("C:C").EntireColumn.AutoFit
Range("D1").Select
ActiveCell.FormulaR1C1 = "Celcius"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("D2").Select
ActiveCell.FormulaR1C1 = "=5/9*(RC[-1]-32)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D26"), Type:=xlFillDefault
Range("D2:D26").Select
Columns("D:D").Select
Selection.NumberFormat = "0.0"
Range("A1:D1").Select
Selection.Font.Bold = True
Columns("C:C").ColumnWidth = 11
Range("A27").Select
ActiveCell.FormulaR1C1 = "Average"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("A27").Select
Selection.Font.Bold = True
Range("D27").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-25]C:R[-1]C)"
Range("D27").Select
Selection.Font.Bold = True
Range("F1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "MediTrax Controls"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "Cheryl Sarver"
.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 = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = False
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating Pivot Table with Macro using Multiple Sheets 0
please change Range("AB30").Select to any cell 3
Macro Error 1
Page Setup not working in macro 1
Macro DoSomething in every cell of my selected range 1
Change code help 0
Printing Setup help 9
Macro takes forever - didn't used to 5

Top