Need Help filtering Pivot Tables in Macro.

[bhuddleston]

Hi, I was wondering if I could get some help with a Macro I'm working on. I am trying to create a Bill of Material Macro, breaking the BOM down by size and separating everything into different sheet tabs. It all seems to work fine until it tries to filter and sort my second pivot table. Any help would be greatly appreciated. Please let me know if any further information is needed. I tried finding the solution online, but it seems like the cause and effect of the error are not always the same.

 Here is the macro I created.

[code]

Sub HangerBOM()
'
' HangerBOM Macro
' CADPipe Hanger BOM
'

'
Sheets("AZLE_LVL_1test").Select
Sheets("AZLE_LVL_1test").Name = "Data"
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Columns("E:E").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.EntireColumn.Hidden = True
Columns("G:G").Select
Selection.EntireColumn.Hidden = True
Columns("A:A").ColumnWidth = 17.43
Columns("B:B").ColumnWidth = 17.43
Columns("H:H").ColumnWidth = 17.43
Columns("I:I").ColumnWidth = 17.43
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R589C9", Version:=6).CreatePivotTable TableDestination:= _
"Sheet1!R3C1", TableName:="PivotTable2", DefaultVersion:=6
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Hanger")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Tag #"), "Sum of Tag #", xlSum
ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of Tag #").Function = _
xlCount
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Support Totals"
Sheets("Data").Select
Sheets.Add After:=ActiveSheet
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "3-8"""
Sheets("Data").Select
Range("K9").Select
ActiveCell.FormulaR1C1 = "Tag #"
Range("K10").Select
ActiveCell.FormulaR1C1 = ">1000"
Sheets("3-8""").Select
Sheets("Data").Range("A1:I99999").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Data").Range("K9:K10"), CopyToRange:=Range( _
"A1:I99999"), Unique:=False
Rows("1:1").Select
Selection.AutoFilter
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Columns("E:E").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.EntireColumn.Hidden = True
Columns("G:G").Select
Selection.EntireColumn.Hidden = True
Columns("A:A").ColumnWidth = 17.43
Selection.ColumnWidth = 8.57
Columns("B:B").ColumnWidth = 17.71
Selection.EntireColumn.Hidden = True
Columns("H:H").ColumnWidth = 17.43
Columns("I:I").ColumnWidth = 17.43
Sheets("3-8""").Select
Sheets("3-8""").Copy After:=Sheets(3)
Sheets("3-8""").Select
Sheets("3-8""").Copy After:=Sheets(4)
Sheets("3-8""").Select
Sheets("3-8""").Copy After:=Sheets(5)
Sheets("3-8""").Select
Sheets("3-8""").Copy After:=Sheets(6)
Sheets("3-8"" (2)").Select
Sheets("3-8"" (2)").Name = "1-2"""
Sheets("3-8"" (3)").Select
Sheets("3-8"" (3)").Name = "5-8"""
Sheets("3-8"" (4)").Select
Sheets("3-8"" (4)").Name = "3-4"""
Sheets("3-8"" (5)").Select
Sheets("3-8"" (5)").Name = "7-8"""
Sheets("3-8""").Select
ActiveSheet.Range("$A$1:$I$589").AutoFilter Field:=1, Criteria1:=">=1000", _
Operator:=xlAnd, Criteria2:="<=1999"
Cells.Select
ActiveWorkbook.Worksheets("3-8""").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("3-8""").Sort.SortFields.Add2 Key:=Range("B2:B589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("3-8""").Sort.SortFields.Add2 Key:=Range("I2:I589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("3-8""").Sort.SortFields.Add2 Key:=Range("H2:H589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("3-8""").Sort
.SetRange Range("A1:I506")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"3-8""!R1C1:R1048576C9", Version:=6).CreatePivotTable TableDestination:= _
"Sheet7!R3C1", TableName:="PivotTable3", DefaultVersion:=6
Sheets("Sheet7").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Type")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Rod Length")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Tag #")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Hanger"), "Count of Hanger", xlCount
ActiveSheet.PivotTables("PivotTable3").PivotSelect "'Tag #'[All]", xlLabelOnly _
+ xlFirstRow, True
ActiveSheet.PivotTables("PivotTable3").PivotFields("Tag #").PivotFilters.Add2 _
Type:=xlCaptionIsBetween, Value1:="1000", Value2:="1999"
Selection.ShowDetail = False
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Sheets("Sheet7").Select
Sheets("Sheet7").Name = "3-8""Totals"
Sheets("1-2""").Select
ActiveSheet.Range("$A$1:$I$589").AutoFilter Field:=1, Criteria1:=">=2000", _
Operator:=xlAnd, Criteria2:="<=2999"
Cells.Select
ActiveWorkbook.Worksheets("1-2""").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1-2""").Sort.SortFields.Add2 Key:=Range("B2:B589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("1-2""").Sort.SortFields.Add2 Key:=Range("I2:I589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("1-2""").Sort.SortFields.Add2 Key:=Range("H2:H589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1-2""").Sort
.SetRange Range("A290:I525")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"1-2""!R1C1:R1048576C9", Version:=6).CreatePivotTable TableDestination:= _
"Sheet8!R3C1", TableName:="PivotTable4", DefaultVersion:=6
Sheets("Sheet8").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Type")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Rod Length")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Tag #")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Hanger"), "Count of Hanger", xlCount
ActiveSheet.PivotTables("PivotTable4").PivotSelect "'Tag #'[All]", xlLabelOnly _
+ xlFirstRow, True
ActiveSheet.PivotTables("PivotTable4").PivotFields("Tag #").PivotFilters.Add2 _
Type:=xlCaptionIsBetween, Value1:="2000", Value2:="2999"
Selection.ShowDetail = False
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Sheets("Sheet8").Select
Sheets("Sheet8").Name = "1-2""Totals"
Sheets("5-8""").Select
ActiveSheet.Range("$A$1:$I$589").AutoFilter Field:=1, Criteria1:=">=3000", _
Operator:=xlAnd, Criteria2:="<=3999"
Cells.Select
ActiveWorkbook.Worksheets("5-8""").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("5-8""").Sort.SortFields.Add2 Key:=Range("B2:B589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("5-8""").Sort.SortFields.Add2 Key:=Range("I2:I589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("5-8""").Sort.SortFields.Add2 Key:=Range("H2:H589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("5-8""").Sort
.SetRange Range("A448:I583")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"5-8""!R1C1:R1048576C9", Version:=6).CreatePivotTable TableDestination:= _
"Sheet9!R3C1", TableName:="PivotTable5", DefaultVersion:=6
Sheets("Sheet9").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Type")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Rod Length")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Tag #")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
"PivotTable5").PivotFields("Hanger"), "Count of Hanger", xlCount
ActiveSheet.PivotTables("PivotTable5").PivotSelect "'Tag #'[All]", xlLabelOnly _
+ xlFirstRow, True
ActiveSheet.PivotTables("PivotTable5").PivotFields("Tag #").PivotFilters.Add2 _
Type:=xlCaptionIsBetween, Value1:="3000", Value2:="3999"
Selection.ShowDetail = False
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Sheets("Sheet9").Select
Sheets("Sheet9").Name = "5-8""Totals"
Sheets("3-4""").Select
ActiveSheet.Range("$A$1:$I$589").AutoFilter Field:=1, Criteria1:=">=4000", _
Operator:=xlAnd, Criteria2:="<=4499"
Cells.Select
ActiveWorkbook.Worksheets("3-4""").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("3-4""").Sort.SortFields.Add2 Key:=Range("B2:B589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("3-4""").Sort.SortFields.Add2 Key:=Range("I2:I589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("3-4""").Sort.SortFields.Add2 Key:=Range("H2:H589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("3-4""").Sort
.SetRange Range("A583:I586")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"3-4""!R1C1:R1048576C9", Version:=6).CreatePivotTable TableDestination:= _
"Sheet10!R3C1", TableName:="PivotTable6", DefaultVersion:=6
Sheets("Sheet10").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Type")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Rod Length")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Tag #")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
"PivotTable6").PivotFields("Hanger"), "Count of Hanger", xlCount
Range("A6").Select
ActiveSheet.PivotTables("PivotTable6").PivotSelect "'Tag #'[All]", xlLabelOnly _
+ xlFirstRow, True
ActiveSheet.PivotTables("PivotTable6").PivotFields("Tag #").PivotFilters.Add2 _
Type:=xlCaptionIsBetween, Value1:="4000", Value2:="4499"
Selection.ShowDetail = False
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Sheets("Sheet10").Select
Sheets("Sheet10").Name = "3-4""Totals"
Sheets("7-8""").Select
ActiveSheet.Range("$A$1:$I$589").AutoFilter Field:=1, Criteria1:=">=4500", _
Operator:=xlAnd, Criteria2:="<=4999"
Cells.Select
ActiveWorkbook.Worksheets("7-8""").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("7-8""").Sort.SortFields.Add2 Key:=Range("B2:B589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("7-8""").Sort.SortFields.Add2 Key:=Range("I2:I589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("7-8""").Sort.SortFields.Add2 Key:=Range("H2:H589") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("7-8""").Sort
.SetRange Range("A586:I589")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"7-8""!R1C1:R1048576C9", Version:=6).CreatePivotTable TableDestination:= _
"Sheet11!R3C1", TableName:="PivotTable7", DefaultVersion:=6
Sheets("Sheet11").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Type")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Rod Length")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Tag #")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
"PivotTable7").PivotFields("Hanger"), "Count of Hanger", xlCount
ActiveSheet.PivotTables("PivotTable7").PivotSelect "'Tag #'[All]", xlLabelOnly _
+ xlFirstRow, True
ActiveSheet.PivotTables("PivotTable7").PivotFields("Tag #").PivotFilters.Add2 _
Type:=xlCaptionIsBetween, Value1:="4500", Value2:="4999"
Selection.ShowDetail = False
Sheets("Sheet11").Select
Sheets("Sheet11").Name = "7-8""Totals"
Sheets(Array("Support Totals", "Data", "3-8""Totals", "3-8""", "1-2""Totals", _
"1-2""", "5-8""Totals", "5-8""", "3-4""Totals", "3-4""", "7-8""Totals", "7-8""")). _
Select
Sheets("Support Totals").Activate
ActiveWindow.View = xlPageLayoutView
ActiveSheet.PageSetup.LeftHeaderPicture.Filename = _
"E:\Z-SkiHi Log\g29941-300x120.jpg"
With ActiveSheet.PageSetup.LeftHeaderPicture
.Height = 54
.Width = 135
End With
Selection.Font.Bold = True
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = "&G"
.CenterHeader = _
"&""-,Bold""&16(Job# - Description)" & Chr(10) & "(Floor - System)" & Chr(10) & "Hanger BOM"
.RightHeader = _
"&D" & Chr(10) & "2943 Stuart Dr." & Chr(10) & "Ft. Worth, Tx." & Chr(10) & "(817)923-0292" & Chr(10) & "Fax:(817)921-0777"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(1.58333333333333)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.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
Application.PrintCommunication = True
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Sheets("Support Totals").Select
ActiveWindow.SmallScroll Down:=15
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = "&G"
.CenterHeader = _
"&""-,Bold""&16(Job# - Description)" & Chr(10) & "(Floor - System)" & Chr(10) & "Hanger BOM"
.RightHeader = _
"&D" & Chr(10) & "2943 Stuart Dr." & Chr(10) & "Ft. Worth, Tx." & Chr(10) & "(817)923-0292" & Chr(10) & "Fax:(817)921-0777"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(1.58333333333333)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.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
Application.PrintCommunication = True
ActiveWindow.SmallScroll Down:=-21
End Sub

[#2]

Any help would be appreciated, the error happens at line Selection.ShowDetail = False Which apparently from what I have read, causes issues?

[#3]

I added the excel data files I am using. The file Hanger BOM - Template-test is the raw data file, while the Hanger BOM - Template-Run, is the file I created the macro in, showing the end result.