Ideas for Cleaning Up Code 1

[Vamphyri]

The following code works fairly well, but I would like to trim it down a bit. I'm sure there are a lot of extra lines of code that I didn't need to put in.

Any ideas about how to "clean up" my code woudl be appreciated.

The code is a Workbook/Open Sub.

The end result takes a table, creates the pivot table, displays all salesperson pages as separate worksheets and then formats each worksheet.

CODE FOLLOWS:____________________________________________

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Bid_Tracking_Data").CreatePivotTable TableDestination:="", TableName:= _
"Quoted Totals"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("Quoted Totals").SmallGrid = False
With ActiveSheet.PivotTables("Quoted Totals").PivotFields("Sales Person")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("Quoted Totals").PivotFields("Project Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Quoted Totals").PivotFields("Bid Sent Date")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("Quoted Totals").PivotFields("Manufacturer")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("Quoted Totals").PivotFields("Product")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("Quoted Totals").PivotFields("Total Quoted Price")
.Orientation = xlDataField
.Position = 1
End With
With ActiveSheet.PivotTables("Quoted Totals").PivotFields( _
"Count of Total Quoted Price")
.Function = xlSum
.NumberFormat = "$#,##0"
End With
Columns("A").Select
Columns("A").EntireColumn.AutoFit
Application.CommandBars("PivotTable").Visible = False
Range("C4").Select
ActiveSheet.PivotTables("Quoted Totals").PivotFields("Manufacturer").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
Range("B4").Select
ActiveSheet.PivotTables("Quoted Totals").PivotFields("Bid Sent Date").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
Range("A4").Select
With ActiveSheet.PivotTables("Quoted Totals").PivotFields("Project Name")
.LayoutBlankLine = True
.LayoutForm = xlOutline
End With
Range("A1").Select
ActiveSheet.PivotTables("Quoted Totals").ShowPages PageField:="Sales Person"
Range("A1").Select
With Selection
.VerticalAlignment = xlCenter
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 11
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 2
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Rows("3:3").Select
Selection.EntireRow.Hidden = True
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("B4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("C4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("D4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("E4").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 9
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Range("A1").Select
Sheets("CL").Select
Range("A1").Select
With Selection
.VerticalAlignment = xlCenter
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 11
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 2
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Rows("3:3").Select
Selection.EntireRow.Hidden = True
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("B4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("C4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("D4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("E4").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 9
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Range("A5").Select
Columns("A").Select
Columns("A").EntireColumn.AutoFit
Range("A1").Select
Sheets("GW").Select
Range("A1").Select
With Selection
.VerticalAlignment = xlCenter
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 11
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 2
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Rows("3:3").Select
Selection.EntireRow.Hidden = True
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("B4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("C4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("D4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("E4").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 9
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Range("A5").Select
Columns("A").Select
Columns("A").EntireColumn.AutoFit
Range("A1").Select
Sheets("JC").Select
Range("A1").Select
With Selection
.VerticalAlignment = xlCenter
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 11
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 2
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Rows("3:3").Select
Selection.EntireRow.Hidden = True
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("B4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("C4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("D4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("E4").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 9
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Range("A5").Select
Columns("A").Select
Columns("A").EntireColumn.AutoFit
Range("A1").Select
Sheets("JH").Select
Range("A1").Select
With Selection
.VerticalAlignment = xlCenter
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 11
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 2
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Rows("3:3").Select
Selection.EntireRow.Hidden = True
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("B4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("C4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("D4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("E4").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 9
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Range("A5").Select
Columns("A").Select
Columns("A").EntireColumn.AutoFit
Range("A1").Select
Sheets("Sheet1").Select
Range("A1").Select
With Selection
.VerticalAlignment = xlCenter
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 11
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 2
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Rows("3:3").Select
Selection.EntireRow.Hidden = True
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("B4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("C4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("D4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("E4").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 9
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Range("A5").Select
Sheets("Sheet1").Name = "Quoted Totals"
Range("A1").Select
Sheets("Bid Tracking").Select
Range("A3").Select

End Sub


In the immortal words of Socrates, who said:
"I drank what?

 

[FXP]

You are listing a number of properties which, I bet, aren't changing:

.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False

Only list those properties which are actually going to change.

 

[SkipVought]

Hi,

You should not need to use a single Activate or Select method until you end and want the display in a particular sheet/cell.

How Can I Make My Code Run Faster? faq707-4105

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Zathras]

Learn how to use subroutines.

For example
[tt]
With ActiveSheet.PivotTables("Quoted Totals").PivotFields("Sales Person")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("Quoted Totals").PivotFields("Project Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Quoted Totals").PivotFields("Bid Sent Date")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("Quoted Totals").PivotFields("Manufacturer")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("Quoted Totals").PivotFields("Product")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("Quoted Totals").PivotFields("Total Quoted Price")
.Orientation = xlDataField
.Position = 1
End With
[/tt]
can be redone as
[tt]
SetOrientation 1, "Sales Person", xlPageField
SetOrientation 1, "Project Name", xlRowField
SetOrientation 2, "Bid Sent Date", xlRowField
SetOrientation 3, "Manufacturer", xlRowField
SetOrientation 4, "Product", xlRowField
SetOrientation 1, "Total Quoted Price", xlDataField
[/tt]
by putting a sub like this in the module:
[tt]
Private Sub (APosition As Integer, AField as String, AOrientation As Integer)
With ActiveSheet.PivotTables("Quoted Totals").PivotFields(AField)
.Orientation = AOrientation
.Position = APosition
End With
End Sub
[/tt]
Similarly, patterns like
[tt]
Range("B4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
[/tt]
can be replaced with (may need other arguments, I didn't anlyze your code completely):
[tt]
DoSetup "B4", xlCenter, xlBottom, 16
DoSetup "C4", xlGeneral, xlCenter, 16
DoSetup "D4", xlGeneral, xlCenter, 16
etc.
[/tt]
by having a sub like this (i.e. this lengthy string of assignments only needs to be in the code once):
[tt]
private sub DoSetup(ARange as string, AHoriz as integer, AVert as integer, ASize as integer )
with Range(ARange)
.HorizontalAlignment = AHoriz
.VerticalAlignment = AVert
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
With .Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = ASize
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 9
End With
With .Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End With
End Sub
[/tt]
Note this code has not been tested. It's just to give you some ideas.

 

[Vamphyri]

Zathras,

Great idea about teh subroutines. I created a "Format_Sales_Tabs" sub and just called it for every sheet.

LIKE THIS:_______________________
Sheets("AF").Select
Format_Sales_Tabs
Sheets("CL").Select
Format_Sales_Tabs
Sheets("GW").Select
Format_Sales_Tabs
Sheets("JC").Select
Format_Sales_Tabs
Sheets("JH").Select
Format_Sales_Tabs

I'm not clear on how to do the first subroutine in your response. Can you offer any clarification?

Sorry. I'm really new (much like the thought of Brittany NOT being a virgin)


In the immortal words of Socrates, who said:
"I drank what?

 

[PHV]

Private Sub [highlight]SetOrientation[/highlight](APosition As Integer, AField as String, AOrientation As Integer)
With ActiveSheet.PivotTables("Quoted Totals").PivotFields(AField)
.Orientation = AOrientation
.Position = APosition
End With
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zathras]

Thanks PHV. Normally I test my stuff before posting, but I ran out of time.

Vamphyri: You are missing one of the most powerful features of subroutines, namely defining and passing arguments. Instead of
[tt]
Sheets("AF").Select
Format_Sales_Tabs
Sheets("CL").Select
Format_Sales_Tabs
Sheets("GW").Select
Format_Sales_Tabs
Sheets("JC").Select
Format_Sales_Tabs
Sheets("JH").Select
Format_Sales_Tabs
[/tt]
you can put the .Select inside the subroutine and just do something like this:
[tt]
Format_Sales_Tabs "AF"
Format_Sales_Tabs "CL"
Format_Sales_Tabs "GW"
Format_Sales_Tabs "JC"
Format_Sales_Tabs "JH"
[/tt]
and inside the sub do like this:
[tt]
Sub Format_Sales_Tabs( ASheetName As String )
Sheets(ASheetName).Select
:
:
[/tt]
And it would go faster (and wouldn't flash on the screen) if you didn't select, but just used the sheet:
[tt]
Sub Format_Sales_Tabs( ASheetName As String )
With Sheets(ASheetName)
:
:
End With
[/tt]
You've made a good start, now keep it up.

p.s., It would be possible to make the sub a little more complex in such a way that it would be possible to replace
[tt]
Format_Sales_Tabs "AF"
Format_Sales_Tabs "CL"
Format_Sales_Tabs "GW"
Format_Sales_Tabs "JC"
Format_Sales_Tabs "JH"
[/tt]
with
[tt]
Format_Sales_Tabs "AF", "CL", "GW", "JC", "JH"
[/tt]
But don't try now. Save that for the advanced class.

 

[corrarjo]

If you find yourself performing the same task more that one time try using some nested loops to go through each worksheet, then each range of cells on each worksheet, and in the middle of the nest you format. If there is different formating based on a particular cell, use some if...else or select case statements to apply some validation. You could try putting the worksheet and range names (for example; worksheet named "GW" and range named "B4") into a couple of string arrays, and use them to loop. If you have 5 worksheets and 20 ranges (cells) then you have to write some code to populate arrays ws and rng with the names of your worksheets and ranges as strings. ws(1) might contain the string "GW", and rng(1) might contain the string "A4". You may have to tweek the following code, but it should point you in the right direction

for i = 0 to 4
for j = 0 to 19
with thisworkbook.worksheets(ws(i)).range(rng(j))
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
with .font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 15
End With
end with
next 'j
next 'i