On Aug 5, 1:59*pm, Don Guillett Excel MVP
wrote:
On Aug 5, 2:48*pm, tbmarlie wrote:





On Aug 5, 11:49*am, Don Guillett Excel MVP
wrote:

At the very least, ALWAYS post your macro for comments.
"If desired, send your file to dguillett I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

I will also email the file to you, but here is the code to the macro.

Sub FormatSUI()
'
' FormatSUI Macro
' Macro recorded 7/27/2010 by MarlieT
'
' Keyboard Shortcut: Ctrl+q
'
* * Rows("1:2").Select
* * Selection.Insert Shift:=xlDown
* * Columns("A:R").Select
* * Columns("A:R").EntireColumn.AutoFit
* * ActiveWindow.SmallScroll ToRight:=-4
* * Range("D1").Select
* * ActiveCell.FormulaR1C1 = "=R[2]C[-1]&"": ""&R[3]C[-1]"
* * Range("E1").Select
* * ActiveCell.FormulaR1C1 = "=R[2]C[9]&"": ""&R[3]C[9]"
* * Range("F1").Select
* * ActiveCell.FormulaR1C1 = "=R[2]C[11]&"": ""&R[3]C[11]"
* * Range("G1").Select
* * ActiveCell.FormulaR1C1 = "=R[2]C[9]&"": ""&R[3]C[9]"
* * Range("D1:M1").Select
* * Selection.Copy
* * Range("D1").Select
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False
* * Columns("D:D").EntireColumn.AutoFit
* * Columns("D:D").ColumnWidth = 15.57
* * Columns("C:C").Select
* * Application.CutCopyMode = False
* * Selection.Delete Shift:=xlToLeft
* * Columns("M:M").Select
* * Selection.Delete Shift:=xlToLeft
* * Columns("N:N").Select
* * Selection.Delete Shift:=xlToLeft
* * Selection.Delete Shift:=xlToLeft
* * Selection.NumberFormat = "mm/dd/yy;@"
* * Columns("E:I").Select
* * Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
* * Columns("K:L").Select
* * Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
* * Rows("3:3").Select
* * Selection.Interior.ColorIndex = xlNone
* * Selection.Font.Bold = True
* * Range("C1:K1").Select
* * Selection.Font.Bold = True
* * Rows("4:4").Select
* * ActiveWindow.FreezePanes = True
* * Range("E3").Select

* * Columns("G:L").Select
* * Columns("G:L").EntireColumn.AutoFit
* * Range("E3").Select

* * *Dim wks As Worksheet
* * *Dim LastCell As Range

* * *Set wks = ActiveSheet

* * *With wks
* * * * *Set LastCell = .Cells(.Rows.Count, "E").End(xlUp)
* * *End With

* * *LastCell.Offset(1, 0).Resize(1, 8).FormulaR1C1 =
"=sum(r3c:r[-1]c)"

* * *Columns("A:C").Select
* * *With Selection
* * * * .HorizontalAlignment = xlCenter
* * * * .VerticalAlignment = xlBottom
* * * * .Orientation = 0
* * * * .AddIndent = False
* * * * .IndentLevel = 0
* * * * .ShrinkToFit = False
* * * * .ReadingOrder = xlContext
* * * * .MergeCells = False
* * End With
* * Range("A1").Select
* * With ActiveSheet.PageSetup
* * * * .LeftHeader = ""
* * * * .CenterHeader = ""
* * * * .RightHeader = ""
* * * * .LeftFooter = ""
* * * * .CenterFooter = ""
* * * * .RightFooter = ""
* * * * .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 = False
* * * * .CenterVertically = False
* * * * .Orientation = xlLandscape
* * * * .Draft = False
* * * * .PaperSize = xlPaperLetter
* * * * .FirstPageNumber = xlAutomatic
* * * * .Order = xlDownThenOver
* * * * .BlackAndWhite = False
* * * * .Zoom = False
* * * * .FitToPagesWide = 1
* * * * .FitToPagesTall = False
* * * * .PrintErrors = xlPrintErrorsDisplayed
* * End With
* * With ActiveSheet.PageSetup
* * * * .LeftMargin = Application.InchesToPoints(0.25)
* * * * .RightMargin = Application.InchesToPoints(0.25)
* * * * .TopMargin = Application.InchesToPoints(0.25)
* * * * .BottomMargin = Application.InchesToPoints(0.25)
* * * * .HeaderMargin = Application.InchesToPoints(0.18)
* * * * .FooterMargin = Application.InchesToPoints(0.18)
* * * * .Zoom = False
* * * * .FitToPagesWide = 1
* * * * .FitToPagesTall = 1
* * ' * *.PrintErrors = -14012
* * End With
* * *With ActiveSheet.PageSetup
* * * * .LeftHeader = ""
* * * * .CenterHeader = ""
* * * * .RightHeader = ""
* * * * .LeftFooter = "Page &P of &N"
* * * * .CenterFooter = ""
* * * * .RightFooter = "&D"
* * * * .LeftMargin = Application.InchesToPoints(0.33)
* * * * .RightMargin = Application.InchesToPoints(0.29)
* * * * .TopMargin = Application.InchesToPoints(0.34)
* * * * .BottomMargin = Application.InchesToPoints(0.47)
* * * * .HeaderMargin = Application.InchesToPoints(0.18)
* * * * .FooterMargin = Application.InchesToPoints(0.23)
* * * * .PrintHeadings = False
* * * * .PrintGridlines = False
* * * * .PrintComments = xlPrintNoComments
* * * * .PrintQuality = 600
* * * * .CenterHorizontally = False
* * * * .CenterVertically = False
* * * * .Orientation = xlLandscape
* * * * .Draft = False
* * * * .PaperSize = xlPaperLetter
* * * * .FirstPageNumber = xlAutomatic
* * * * .Order = xlDownThenOver
* * * * .BlackAndWhite = False
* * * * .Zoom = False
* * * * .FitToPagesWide = 1
* * * * .FitToPagesTall = False
* * * * .PrintErrors = xlPrintErrorsDisplayed
* * End With
* * ActiveWindow.SelectedSheets.PrintPreview
End Sub

I have not seen the file in my inbox. Send to dguillett@ gmail.com
(remove the space in the email addy)- Hide quoted text -

- Show quoted text -

Sorry, I got caught in the middle of something else. I just sent it.
Thanks for your help.