Free Microsoft Excel 2013 Quick Reference

Show only the subtotals and grand total in a pivot table Results

Morning! I have a pivot table and I need to create another report from this
pivot table that only shows the sub totals and totals . How do I do this?
thanks

Excel 2003

I am pretty good at pivot table but can't figure out how the have subtotals
display for my 3rd row field in under my 2nd row field - they only display at
the grand total line.

Please see the example below. I need to get subtotals for A/P and OP under
store 1 and then again under store 2.

Also My calculated field do now show up in my grand totals? why not - they
should work - the math is valid.

thanks!!!!

Div Desc Desc Ver
Store 1 4 OP
A/P
1 OP
A/P
2 OP
A/P
3 OP
A/P
Store 1 Total
Store 2 5 OP
A/P
6 OP
A/P
7 OP
A/P
8 OP
A/P
Store 2 Total

The SUBTOTAL function in Excel will subtotal a list and show an outline
control on the left of the screen. The outline control allows the user to
change the amount of data shown in the sheet (data with subtotals, only
subtotals or only grand total). Can this outline control be programmed via
VBA? I would like to have a list that only shows the subtotals (without the
source data displayed)? Will I have to switch to a Pivot Table to
accomplish this task?

Thanks

Any assistance appriciated. (Excel 2003)

1) I have a simple Pivot table showing 6 groups with their totals and a
subtotal eg:
America 10
UK 15
Oz 22 etc.
Grand Total 125

I want to Pivot Graph this data, no problems. I want however an extra line
to show the total on the Secondary Axis. I have almost got it by placing
the column in a second time as a Running Total and then as a Line on my
chart, hiding all the values until the last point.

However I would like if possible the Total as a horizontal line across the
chart?

2) I have come across Grouping Dates and the inability to Group if there
are blanks. I thaught about generating an invalid date eg 01/01/1900, to
use that and ignore it later, I thaught about sorting and inserting a row to
knock the blanks out of my pivot using a macro.....

Then a thaught, I tried linking the data into Access 2003 and writing a
Pivot Chart query and it worked. I am VERY happy to discover this, only a
simple questions, was it possible in Excel....?

Roger Knowles
MOS Master all Versions
England.

I know there are compatibility issues between Excel 2003 and 2007. So, I'm pretty sure I already know my answer, but I need to ask so I can rest easy.

A co-worker built a macro in 2007. However, a majority of my company does not have 2007, only 2003. These people need this macro loaded on their PC, but the macro doesn't work on 2003.

Is there an easy "fix", or does the code need to be re-written in 2003?

Here's the code: (suggestions on making this run faster and more efficient are welcome)


	VB:
	
 EMS_Match_Report1() 
     '
     ' Macro1 Macro
     '
     
     '
    Application.ScreenUpdating = False 
    Cells.Select 
    Cells.EntireColumn.AutoFit 
    Columns("D:D").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("E:H").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("H:M").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("I:J").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("K:M").Select 
    Selection.Delete Shift:=xlToLeft 
    ActiveWindow.SmallScroll ToRight:=3 
    Columns("M:M").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("N:N").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("P:Q").Select 
    Columns("Q:U").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("U:U").Select 
    ActiveWindow.SmallScroll ToRight:=7 
    Columns("Y:Y").Select 
    ActiveWindow.SmallScroll ToRight:=10 
    Columns("Y:AH").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("Z:Z").Select 
    ActiveWindow.SmallScroll ToRight:=18 
    Columns("Z:AM").Select 
    Selection.Delete Shift:=xlToLeft 
    ActiveWindow.SmallScroll ToRight:=-21 
    Columns("D:D").Select 
    Selection.Cut 
    Columns("C:C").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("O:O").Select 
    Selection.Cut 
    Columns("F:F").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("Q:Q").Select 
    Selection.Cut 
    Columns("G:G").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("R:R").Select 
    Selection.Cut 
    Columns("H:H").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("R:R").Select 
    Selection.Cut 
    Columns("I:I").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("O:O").Select 
    Selection.Cut 
    Columns("J:J").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("Q:Q").Select 
    Selection.Cut 
    Columns("K:K").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("R:R").Select 
    Selection.Cut 
    Columns("L:L").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("R:R").Select 
    Selection.Cut 
    Columns("M:M").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("N:O").Select 
    Selection.Cut 
    Columns("S:S").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("Y:Y").Select 
    Selection.Cut 
    Columns("U:U").Select 
    Selection.Insert Shift:=xlToRight 
    ActiveWindow.SmallScroll ToRight:=-12 
    Range("A1").Select 
    With ActiveWindow 
        .SplitColumn = 0 
        .SplitRow = 1 
    End With 
    ActiveWindow.FreezePanes = True 
    Cells.Select 
    Cells.EntireColumn.AutoFit 
    Rows("1:1").Select 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
        .WrapText = False 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Selection.Font.Bold = True 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
        .WrapText = True 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Selection.RowHeight = 119.25 
    Cells.Select 
    Selection.ColumnWidth = 34 
    Cells.EntireColumn.AutoFit 
    Cells.EntireColumn.AutoFit 
    Columns("A:A").Select 
    Selection.ColumnWidth = 18.43 
    Columns("A:A").EntireColumn.AutoFit 
    Columns("F:M").Select 
    Selection.ColumnWidth = 7 
    Selection.ColumnWidth = 5.71 
    Columns("F:M").EntireColumn.AutoFit 
    Columns("N:R").Select 
    Selection.ColumnWidth = 5 
    Columns("N:R").EntireColumn.AutoFit 
    Columns("S:Y").Select 
    Columns("S:Y").EntireColumn.AutoFit 
    Selection.ColumnWidth = 7.14 
    Columns("S:Y").EntireColumn.AutoFit 
    Range("R1").Select 
    ActiveCell.FormulaR1C1 = "Fran $" 
    With ActiveCell.Characters(Start:=1, Length:=6).Font 
        .Name = "Calibri" 
        .FontStyle = "Bold" 
        .Size = 11 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ThemeColor = xlThemeColorLight1 
        .TintAndShade = 0 
        .ThemeFont = xlThemeFontMinor 
    End With 
    Range("R2").Select 
    Columns("R:R").ColumnWidth = 5 
    Columns("R:R").EntireColumn.AutoFit 
    ActiveWindow.SmallScroll ToRight:=-14 
    Range("B1").Select 
    ActiveCell.FormulaR1C1 = "Mfr Part Number (from Oppslist)" 
    With ActiveCell.Characters(Start:=1, Length:=31).Font 
        .Name = "Calibri" 
        .FontStyle = "Bold" 
        .Size = 11 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ThemeColor = xlThemeColorLight1 
        .TintAndShade = 0 
        .ThemeFont = xlThemeFontMinor 
    End With 
    Cells.Select 
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Clear 
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Range( _ 
    "B2:B4877"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ 
    xlSortNormal 
    With ActiveWorkbook.Worksheets(1).Sort 
        .SetRange Range("A1:CF4877") 
        .Header = xlYes 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
        .Apply 
    End With 
     ' Delete_Blank_Row_Test Macro
     'Columns("B:B").Select
     'Selection.SpecialCells(xlCellTypeBlanks).Select
     'Selection.EntireRow.Delete
     'Range("A2").Select
     ' Delete_Dups_Test Macro
    ActiveSheet.Range("$A$1:$CF$4877").RemoveDuplicates Columns:=Array(1, 19, 20, 23 _ 
    ), Header:=xlNo 
     ' Sort Macro
    Cells.Select 
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Clear 
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Range( _ 
    "A2:A18594"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ 
    xlSortNormal 
    With ActiveWorkbook.Worksheets(1).Sort 
        .SetRange Range("A1:CF18594") 
        .Header = xlYes 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
        .Apply 
    End With 
     ' Start Blank IPN Cells Macro
    Columns("C:C").Select 
    Selection.Replace What:="", Replacement:="blank", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
    Range("A2").Select 
     ' End Blank IPN Cell Macro
     ' Numbering
    Application.DisplayAlerts = False 
    Columns("A:A").Select 
    Selection.Copy 
    Sheets.Add After:=Sheets(Sheets.Count) 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    ActiveSheet.Range("$A$1:$A$65000").RemoveDuplicates Columns:=1, Header:=xlNo 
    Range("B2").Select 
     'Range("A2").Select
     'Columns("B:B").Select
     'Range("B2").Activate
     'Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
     'Range("B1").Select
     'ActiveCell.FormulaR1C1 = "CT"
    Range("B2").Select 
    ActiveCell.FormulaR1C1 = "1" 
    Range("B3").Select 
    ActiveCell.FormulaR1C1 = "=R[-1]C+1" 
    Range("B3").Select 
    If IsEmpty(ActiveCell) Then Exit Sub 
    Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown 
     'VLookUp Part
    Sheets(2).Select 
    Sheets(2).Name = "Numbers" 
    Sheets(1).Select 
    Columns("B:B").Select 
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 
    Range("B2").Select 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Numbers!C[-1]:C,2,FALSE)" 
    If IsEmpty(ActiveCell) Then Exit Sub 
    Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown 
     'Start Copy Paste Special
    Range("B2").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Range("B2").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     'Stop Copy Paste Special
    Range("B1").Select 
    ActiveCell.FormulaR1C1 = "#" 
    With ActiveCell.Characters(Start:=1, Length:=1).Font 
        .Name = "Calibri" 
        .FontStyle = "Bold" 
        .Size = 11 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ThemeColor = xlThemeColorLight1 
        .TintAndShade = 0 
        .ThemeFont = xlThemeFontMinor 
    End With 
    Columns("B:B").Select 
    Selection.Font.Bold = False 
    Selection.Font.Bold = True 
    Columns("B:B").Select 
    Selection.Cut 
    Columns("A:A").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("A:A").EntireColumn.AutoFit 
    Range("A2").Select 
    Sheets("Numbers").Select 
    ActiveWindow.SelectedSheets.Delete 
     ' Border_and_Coloring_Formatting Macro
    Range("A:B,D:F").Select 
    Range("D1").Activate 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=LEN(TRIM(D1))>0" 
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
    With Selection.FormatConditions(1).Borders(xlLeft) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Borders(xlRight) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Borders(xlTop) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Borders(xlBottom) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Interior 
        .PatternColorIndex = xlAutomatic 
        .Color = 10092543 
        .TintAndShade = 0 
    End With 
    Selection.FormatConditions(1).StopIfTrue = True 
    Columns("G:N").Select 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=LEN(TRIM(G1))>0" 
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
    With Selection.FormatConditions(1).Borders(xlLeft) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Borders(xlRight) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Borders(xlTop) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Borders(xlBottom) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Interior 
        .PatternColorIndex = xlAutomatic 
        .ThemeColor = xlThemeColorAccent1 
        .TintAndShade = 0.799981688894314 
    End With 
    Selection.FormatConditions(1).StopIfTrue = True 
    Columns("O:S").Select 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=LEN(TRIM(O1))>0" 
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
    With Selection.FormatConditions(1).Borders(xlLeft) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Borders(xlRight) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Borders(xlTop) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Borders(xlBottom) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Interior 
        .PatternColorIndex = xlAutomatic 
        .ThemeColor = xlThemeColorAccent4 
        .TintAndShade = 0.799981688894314 
    End With 
    Selection.FormatConditions(1).StopIfTrue = True 
    Columns("C:C").Select 
    Range("C:C,T:Z").Select 
    Range("T1").Activate 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=LEN(TRIM(T1))>0" 
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
    With Selection.FormatConditions(1).Borders(xlLeft) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Borders(xlRight) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Borders(xlTop) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.FormatConditions(1).Borders(xlBottom) 
        .LineStyle = xlContinuous 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    Selection.FormatConditions(1).StopIfTrue = True 
    Range("A2").Select 
     ' Copy Worksheets and Renaming
    Range("A2").Select 
    Sheets(1).Select 
    Sheets(1).Copy After:=Sheets(1) 
    Sheets(2).Select 
    Sheets(2).Copy After:=Sheets(2) 
    Sheets(3).Select 
    Sheets(3).Name = "Pivot Table" 
    Columns("A:A").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("B:R").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("D:D").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("E:E").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("F:F").Select 
    Selection.Delete Shift:=xlToLeft 
    Range("A2").Select 
    Sheets(2).Select 
    Sheets(2).Name = "Detailed Match" 
    Sheets(1).Select 
    Sheets(1).Name = "Match Summary" 
    Sheets("Match Summary").Select 
    With ActiveWorkbook.Sheets("Match Summary").Tab 
        .Color = 255 
        .TintAndShade = 0 
    End With 
    Columns("C:C").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("S:S").Select 
    Columns("S:Z").Select 
    Selection.Delete Shift:=xlToLeft 
    Range("A2").Select 
    Application.ScreenUpdating = True 
     ' Removes Dups in Col A in Match Summary Sheet
    Sheets("Match Summary").Select 
    Columns("A:R").Select 
    ActiveSheet.Range("$A$1:$R$65000").RemoveDuplicates Columns:=1, Header:=xlNo 
    Range("A2").Select 
     ' Subtotal Average on Detailed Match Sheet
    Sheets("Detailed Match").Select 
    Selection.Copy 
    Range("A2").Select 
    Application.CutCopyMode = False 
    Columns("A:U").Select 
    Selection.Subtotal GroupBy:=2, Function:=xlAverage, TotalList:=Array(5, 6) _ 
    , Replace:=True, PageBreaks:=False, SummaryBelowData:=True 
    Columns("B:B").Select 
    Selection.Replace What:=" AVerage", Replacement:="", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
    Sheets("Match Summary").Select 
     'Columns("S:S").Select
     'Selection.Delete Shift:=xlToLeft
    Range("A2").Select 
     ' Pivot Table 1
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ 
    "Pivot Table!R1C1:R1048576C3", Version:=xlPivotTableVersion12). _ 
    CreatePivotTable TableDestination:="", TableName:="PivotTable1" _ 
    , DefaultVersion:=xlPivotTableVersion12 
    With ActiveSheet.PivotTables("PivotTable1").PivotFields( _ 
        "MPN (from Your Customer List)") 
        .Orientation = xlRowField 
        .Position = 1 
    End With 
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable1").PivotFields("Listed Qty"), "Count of Listed Qty", xlCount 
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Listed Qty") 
        .Caption = "Sum of Total Listed Avail. Qty" 
        .Function = xlSum 
    End With 
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable1").PivotFields("Listed Cost"), "Count of Listed Cost", xlCount 
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Listed Cost") 
        .Caption = "Min of Listed Cost" 
        .Function = xlMin 
    End With 
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable1").PivotFields("Listed Cost"), "Count of Listed Cost", xlCount 
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Listed Cost") 
        .Caption = "Max of Listed Cost" 
        .Function = xlMax 
    End With 
    ActiveWorkbook.ShowPivotTableFieldList = False 
    Columns("A:D").Select 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    Rows("1:1").Select 
    Selection.Delete Shift:=xlUp 
    Columns("A:D").Select 
    Selection.Copy 
    Sheets("Match Summary").Select 
    Columns("S:S").Select 
    ActiveSheet.Paste 
    Sheets(1).Select 
    Application.CutCopyMode = False 
    ActiveWindow.SelectedSheets.Delete 
    Sheets("Pivot Table").Select 
    Columns("B:C").Select 
    Selection.Delete Shift:=xlToLeft 
    Range("A2").Select 
     'Delete Dups in Pivot Table
    Sheets("Pivot Table").Select 
    Columns("A:C").Select 
    ActiveSheet.Range("$A$1:$C$65000").RemoveDuplicates Columns:=Array(1, 2, 3), _ 
    Header:=xlNo 
     ' Pivot Table 2
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ 
    "Pivot Table!R1C1:R1048576C3", Version:=xlPivotTableVersion12). _ 
    CreatePivotTable TableDestination:="", TableName:="PivotTable1" _ 
    , DefaultVersion:=xlPivotTableVersion12 
    ActiveWorkbook.ShowPivotTableFieldList = True 
    With ActiveSheet.PivotTables("PivotTable1").PivotFields( _ 
        "MPN (from Your Customer List)") 
        .Orientation = xlRowField 
        .Position = 1 
    End With 
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("A2E Contact") 
        .Orientation = xlColumnField 
        .Position = 1 
    End With 
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable1").PivotFields("File#"), "Count of File#", xlCount 
    ActiveWorkbook.ShowPivotTableFieldList = False 
     'Begin Test PT Macro
    With ActiveSheet.PivotTables("Pivottable1") 
        .ColumnGrand = False 
        .DisplayNullString = True 
        .RowGrand = False 
    End With 
    With ActiveSheet.PivotTables("Pivottable1").PivotFields( _ 
        "MPN (from Your Customer List)") 
        .PivotItems("(blank)").Visible = False 
    End With 
     'End Test PT Macro
    Cells.Select 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    Rows("1:1").Select 
    Selection.Delete Shift:=xlUp 
    Range("A1").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Copy 
    Sheets("Match Summary").Select 
    Range("W1").Select 
    ActiveSheet.Paste 
    Sheets(3).Select 
    Application.CutCopyMode = False 
    ActiveWindow.SelectedSheets.Delete 
    Sheets("Match Summary").Select 
     ' Formatting_After_PT Macro
    Columns("B:B").Select 
    Selection.SpecialCells(xlCellTypeBlanks).Select 
    Selection.EntireRow.Delete 
    Columns("S:S").Select 
    Selection.Delete Shift:=xlToLeft 
    ActiveWindow.SmallScroll ToRight:=7 
    Columns("V:V").Select 
    Selection.ClearContents 
    Range("V1").Select 
    ActiveCell.FormulaR1C1 = "Total Excess / LDT Reps Showing Avail" 
    Range("S1:V1").Select 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
        .WrapText = False 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Selection.Font.Bold = True 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
        .WrapText = True 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Columns("S:S").ColumnWidth = 6.71 
    Columns("T:V").Select 
    Selection.ColumnWidth = 5.43 
    Columns("T:U").Select 
    Columns("T:U").EntireColumn.AutoFit 
    Selection.ColumnWidth = 5.29 
    Range("V1").Select 
    Columns("V:V").ColumnWidth = 8.43 
    Range("I1").Select 
    ActiveCell.FormulaR1C1 = "Res. Qty" 
    With ActiveCell.Characters(Start:=1, Length:=8).Font 
        .Name = "Calibri" 
        .FontStyle = "Bold" 
        .Size = 11 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ThemeColor = xlThemeColorLight1 
        .TintAndShade = 0 
        .ThemeFont = xlThemeFontMinor 
    End With 
    Range("A2").Select 
     ' Pt Formatting 2
    Range("W1").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    With Selection 
        .HorizontalAlignment = xlGeneral 
        .VerticalAlignment = xlBottom 
        .WrapText = False 
        .Orientation = 90 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Selection.Font.Bold = True 
    ActiveWindow.SmallScroll ToRight:=-15 
    Columns("W:W").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
     'Columns("W:DZ").EntireColumn.AutoFit
    Columns.EntireColumn.AutoFit 
    ActiveWindow.SmallScroll ToRight:=-18 
     ' Delete Pivot Table Sheet Macro
    Sheets("Pivot Table").Select 
    ActiveWindow.SelectedSheets.Delete 
    Sheets("Match Summary").Select 
    Range("A2").Select 
    Application.DisplayAlerts = True 
     ' Macro2 Macro
    Columns("A:A").Select 
    Selection.SpecialCells(xlCellTypeBlanks).Select 
    Selection.EntireRow.Delete 
    Range("S1").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
    With Selection.Borders(xlEdgeLeft) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeTop) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeBottom) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeRight) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideVertical) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideHorizontal) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
     ' Macro1 Macro
    Sheets("Detailed Match").Select 
    Columns("B:B").Select 
    Selection.Replace What:="Grand", Replacement:="", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
    Selection.SpecialCells(xlCellTypeBlanks).Select 
    Selection.EntireRow.Delete 
    ActiveWorkbook.Save 
     ' Format Detail Match Sheet
    Range("A:A,D:D").Select 
    Application.ScreenUpdating = False 
    Selection.SpecialCells(xlCellTypeBlanks).Select 
    Selection.FormulaR1C1 = "=R[-1]C" 
    Range("A1").Select 
     ' Start IPN Macro
    Range("D1").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
    With Selection.Borders(xlEdgeLeft) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeTop) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeBottom) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeRight) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideVertical) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideHorizontal) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Interior 
        .Pattern = xlSolid 
        .PatternColorIndex = xlAutomatic 
        .Color = 10092543 
        .TintAndShade = 0 
        .PatternTintAndShade = 0 
    End With 
    Columns("D:D").Select 
    Selection.Replace What:="blank", Replacement:="", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
     'Stop IPN Macro
     'Application.ScreenUpdating = True
    Range("C:C,G:Z").Select 
    Range("G1").Activate 
    Selection.Replace What:="", Replacement:="blank", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
    ActiveWindow.SmallScroll ToRight:=-2 
    Range("C1").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
    With Selection.Borders(xlEdgeLeft) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeTop) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeBottom) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeRight) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideVertical) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideHorizontal) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    Range("G1").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Range("N1").Select 
    ActiveWindow.SmallScroll ToRight:=-5 
    Range("G1:N1").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
    With Selection.Borders(xlEdgeLeft) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeTop) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeBottom) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeRight) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideVertical) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideHorizontal) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Interior 
        .Pattern = xlSolid 
        .PatternColorIndex = xlAutomatic 
        .ThemeColor = xlThemeColorAccent1 
        .TintAndShade = 0.799981688894314 
        .PatternTintAndShade = 0 
    End With 
    Range("O1:S1").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
    With Selection.Borders(xlEdgeLeft) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeTop) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeBottom) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeRight) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideVertical) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideHorizontal) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Interior 
        .Pattern = xlSolid 
        .PatternColorIndex = xlAutomatic 
        .ThemeColor = xlThemeColorAccent4 
        .TintAndShade = 0.799981688894314 
        .PatternTintAndShade = 0 
    End With 
    Range("T1:Z1").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
    With Selection.Borders(xlEdgeLeft) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeTop) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeBottom) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeRight) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideVertical) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideHorizontal) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    Range("C:C,G:Z").Select 
    Range("G1").Activate 
    Selection.Replace What:="blank", Replacement:="", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
    Columns("G:N").Select 
    Selection.Columns.Group 
    ActiveSheet.Outline.ShowLevels RowLevels:=2 
    Range("A3").Select 
    Sheets(2).Select 
    Sheets(2).Name = "Detailed Match" 
    Sheets("Match Summary").Select 
    Columns("F:M").Select 
    Selection.Columns.Group 
    Columns("A:A").Select 
    Selection.SpecialCells(xlCellTypeBlanks).Select 
    Selection.EntireRow.Delete 
    ActiveWorkbook.Save 
    Sheets(1).Select 
    Sheets(1).Name = "Match Summary" 
    Range("B2").Select 
    Columns("W:W").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Selection.Replace What:="", Replacement:="+", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
    Range("W1").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Range(Selection, Selection.End(xlDown)).Select 
    With Selection.Interior 
        .Pattern = xlSolid 
        .PatternColorIndex = xlAutomatic 
        .ThemeColor = xlThemeColorAccent6 
        .TintAndShade = 0.799981688894314 
        .PatternTintAndShade = 0 
    End With 
    Selection.Replace What:="+", Replacement:="", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
    Sheets("Detailed Match").Select 
    Columns("C:D").Select 
    Selection.Columns.Group 
    Columns("X:X").Select 
    Sheets("Match Summary").Select 
    Columns("C:C").Select 
    Selection.Columns.Group 
    Sheets("Detailed Match").Select 
    Range("A3").Select 
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2 
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1 
    Sheets("Match Summary").Select 
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1 
    Range("B2").Select 
     ' Start IPN Macro
    Range("C1").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
    With Selection.Borders(xlEdgeLeft) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeTop) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeBottom) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlEdgeRight) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideVertical) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Borders(xlInsideHorizontal) 
        .LineStyle = xlContinuous 
        .ColorIndex = 0 
        .TintAndShade = 0 
        .Weight = xlThin 
    End With 
    With Selection.Interior 
        .Pattern = xlSolid 
        .PatternColorIndex = xlAutomatic 
        .Color = 10092543 
        .TintAndShade = 0 
        .PatternTintAndShade = 0 
    End With 
    Columns("C:C").Select 
    Selection.Replace What:="blank", Replacement:="", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
     ' Count Macro
    Columns("W:W").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Selection.Replace What:="", Replacement:="+", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
    Range("V2").Select 
    ActiveCell.FormulaR1C1 = "=COUNT(RC[1]:RC[500])" 
    If IsEmpty(ActiveCell) Then Exit Sub 
    Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown 
    Columns("W:W").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Selection.Replace What:="+", Replacement:="", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
    Range("V2").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Selection.Font.Bold = True 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
        .WrapText = False 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Range("A2").Select 
     ' Delete EMS/OEM Name Column Macro
    Sheets("Detailed Match").Select 
    Columns("X:X").Select 
    Selection.Delete Shift:=xlToLeft 
    Range("B9").Select 
    Sheets("Match Summary").Select 
    Range("B2").Select 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines