As many of you will probably be able to tell from my following issues, I am quite new to using Macros & making adjustments to VBA code. While I definitely feel that I've learned quite a bit scouring this website throughout the last few days, I wasn't able to find the perfect post to assist me with my current issues.
I am re-vamping a current metric reporting process at work & using am using macros to assist in that effort. Here is a breakdown of my current issues:
My current workbook contains 3 separate macros that generate 3 new sheets, each containing pivot tables. As you will see in the code, further analysis is preformed once the pivot tables are created. The first two macros are virtually identical in process, yet pull data from 2 seperate data dump sheets within the workbook. I currently have these first 2 macros working almost perfectly, in that they provide a correct analysis of the data - however, I am unable to correctly adjust the VBA so that they produce a specifically named new sheet in the workbook. Each time these macros are run, they simply add a new sheet titled "Sheet1" "Sheet2" Sheet3" etc..... For my purposes, I need a consistantly named sheet so I can reference these pivot tables to a summary report on a different sheet within the workbook. I need these sheets to be named "In Closure Pivot" and "POP Pivot," respectively. As I mentioned, these first 2 macros are practically identical, so I will just incluse the VBA code for one of them.
Thanks again,
Sam
I am re-vamping a current metric reporting process at work & using am using macros to assist in that effort. Here is a breakdown of my current issues:
My current workbook contains 3 separate macros that generate 3 new sheets, each containing pivot tables. As you will see in the code, further analysis is preformed once the pivot tables are created. The first two macros are virtually identical in process, yet pull data from 2 seperate data dump sheets within the workbook. I currently have these first 2 macros working almost perfectly, in that they provide a correct analysis of the data - however, I am unable to correctly adjust the VBA so that they produce a specifically named new sheet in the workbook. Each time these macros are run, they simply add a new sheet titled "Sheet1" "Sheet2" Sheet3" etc..... For my purposes, I need a consistantly named sheet so I can reference these pivot tables to a summary report on a different sheet within the workbook. I need these sheets to be named "In Closure Pivot" and "POP Pivot," respectively. As I mentioned, these first 2 macros are practically identical, so I will just incluse the VBA code for one of them.
Rich (BB code):
Sub In_Closure_Stats()
'
' In_Closure_Stats Macro
'
'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"RMS_TOTAL", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="", TableName:="PivotTable8", DefaultVersion _
:=xlPivotTableVersion14
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Status")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields( _
"Contract Type (Cntrct #) (Curr)")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Purchase Order Number" _
)
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable8").PivotFields("Status").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Status")
.PivotItems("CLSD").Visible = False
.PivotItems("INIT").Visible = False
End With
ActiveSheet.PivotTables("PivotTable8").PivotFields("Status"). _
EnableMultiplePageItems = True
ActiveSheet.PivotTables("PivotTable8").PivotFields( _
"Contract Type (Cntrct #) (Curr)").CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable8").PivotFields( _
"Contract Type (Cntrct #) (Curr)")
.PivotItems("").Visible = False
.PivotItems("Advance/Performance").Visible = False
.PivotItems("Fixed Price Contract").Visible = False
.PivotItems("Prog. Pay Contract").Visible = False
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable8").PivotFields( _
"Contract Type (Cntrct #) (Curr)").EnableMultiplePageItems = True
Range("B5").Select
ActiveCell.FormulaR1C1 = _
"=+VLOOKUP(RC[-1],'Net Unbilled Reference'!C10:C11,2,FALSE)"
Range("B5").Select
Selection.AutoFill Destination:=Range("B5:B1129")
Range("B5:B1129").Select
Range("B21").Select
Selection.End(xlDown).Select
ActiveWindow.SmallScroll Down:=51
Selection.Copy
ActiveWindow.ScrollRow = 1128
ActiveWindow.ScrollRow = 1139
ActiveWindow.ScrollRow = 1150
ActiveWindow.ScrollRow = 1161
ActiveWindow.ScrollRow = 1171
ActiveWindow.ScrollRow = 1193
ActiveWindow.ScrollRow = 1204
ActiveWindow.ScrollRow = 1214
ActiveWindow.ScrollRow = 1225
ActiveWindow.ScrollRow = 1236
ActiveWindow.ScrollRow = 1247
ActiveWindow.ScrollRow = 1257
ActiveWindow.ScrollRow = 1268
ActiveWindow.ScrollRow = 1279
ActiveWindow.ScrollRow = 1289
ActiveWindow.ScrollRow = 1300
ActiveWindow.ScrollRow = 1311
ActiveWindow.ScrollRow = 1322
ActiveWindow.ScrollRow = 1332
ActiveWindow.ScrollRow = 1343
ActiveWindow.ScrollRow = 1354
ActiveWindow.ScrollRow = 1365
ActiveWindow.ScrollRow = 1375
ActiveWindow.ScrollRow = 1386
ActiveWindow.ScrollRow = 1408
ActiveWindow.ScrollRow = 1418
ActiveWindow.ScrollRow = 1429
ActiveWindow.ScrollRow = 1461
ActiveWindow.ScrollRow = 1472
ActiveWindow.ScrollRow = 1483
ActiveWindow.ScrollRow = 1504
ActiveWindow.ScrollRow = 1526
ActiveWindow.ScrollRow = 1547
ActiveWindow.ScrollRow = 1569
ActiveWindow.ScrollRow = 1590
ActiveWindow.ScrollRow = 1612
ActiveWindow.ScrollRow = 1633
ActiveWindow.ScrollRow = 1644
ActiveWindow.ScrollRow = 1676
ActiveWindow.ScrollRow = 1697
ActiveWindow.ScrollRow = 1708
ActiveWindow.ScrollRow = 1730
ActiveWindow.ScrollRow = 1740
ActiveWindow.ScrollRow = 1751
ActiveWindow.ScrollRow = 1773
ActiveWindow.ScrollRow = 1783
ActiveWindow.ScrollRow = 1805
ActiveWindow.ScrollRow = 1816
ActiveWindow.ScrollRow = 1837
ActiveWindow.ScrollRow = 1869
ActiveWindow.ScrollRow = 1891
ActiveWindow.ScrollRow = 1912
ActiveWindow.ScrollRow = 1923
ActiveWindow.ScrollRow = 1944
ActiveWindow.ScrollRow = 1955
ActiveWindow.ScrollRow = 1966
ActiveWindow.ScrollRow = 1977
ActiveWindow.ScrollRow = 1987
ActiveWindow.ScrollRow = 1998
ActiveWindow.ScrollRow = 2009
ActiveWindow.ScrollRow = 2020
ActiveWindow.ScrollRow = 2030
ActiveWindow.ScrollRow = 2041
ActiveWindow.ScrollRow = 2063
ActiveWindow.ScrollRow = 2073
ActiveWindow.ScrollRow = 2084
ActiveWindow.ScrollRow = 2095
ActiveWindow.ScrollRow = 2116
ActiveWindow.ScrollRow = 2138
ActiveWindow.ScrollRow = 2148
ActiveWindow.ScrollRow = 2159
ActiveWindow.ScrollRow = 2170
ActiveWindow.ScrollRow = 2181
ActiveWindow.ScrollRow = 2191
ActiveWindow.ScrollRow = 2181
ActiveWindow.ScrollRow = 2170
ActiveWindow.ScrollRow = 2159
ActiveWindow.ScrollRow = 2148
ActiveWindow.ScrollRow = 2138
ActiveWindow.ScrollRow = 2148
ActiveWindow.ScrollRow = 2159
ActiveWindow.ScrollRow = 2170
ActiveWindow.ScrollRow = 2191
ActiveWindow.ScrollRow = 2202
ActiveWindow.ScrollRow = 2213
ActiveWindow.ScrollRow = 2224
ActiveWindow.ScrollRow = 2234
ActiveWindow.ScrollRow = 2245
ActiveWindow.ScrollRow = 2256
ActiveWindow.ScrollRow = 2267
ActiveWindow.ScrollRow = 2277
ActiveWindow.ScrollRow = 2288
ActiveWindow.ScrollRow = 2299
ActiveWindow.ScrollRow = 2309
ActiveWindow.ScrollRow = 2320
ActiveWindow.ScrollRow = 2331
ActiveWindow.ScrollRow = 2342
ActiveWindow.ScrollRow = 2352
ActiveWindow.ScrollRow = 2363
ActiveWindow.ScrollRow = 2374
ActiveWindow.ScrollRow = 2385
ActiveWindow.ScrollRow = 2395
ActiveWindow.ScrollRow = 2406
ActiveWindow.ScrollRow = 2417
ActiveWindow.ScrollRow = 2428
ActiveWindow.ScrollRow = 2438
ActiveWindow.ScrollRow = 2449
ActiveWindow.ScrollRow = 2460
ActiveWindow.ScrollRow = 2471
ActiveWindow.ScrollRow = 2481
ActiveWindow.ScrollRow = 2492
Range("B1129:B2500").Select
ActiveSheet.Paste
Range("B1118").Select
Selection.End(xlUp).Select
Range("B5").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False
Selection.Style = "Currency"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=+COUNTIF(RC[-3]:RC[-3],RC[-3])"
Range("D5").Select
Selection.AutoFill Destination:=Range("D5:D2500"), Type:=xlFillDefault
Range("D5:D2500").Select
Range("D2496").Select
Selection.End(xlUp).Select
Range("D5").Select
Columns("E:E").ColumnWidth = 19.83
Columns("F:F").ColumnWidth = 18
Range("E5").Select
ActiveCell.FormulaR1C1 = "In Closure Unbilled CIP"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=+SUM(RC[-4]:R[2495]C[-4])/1000"
Range("F5").Select
Selection.Style = "Currency"
Range("E6").Select
ActiveCell.FormulaR1C1 = "Contracts"
Range("F6").Select
ActiveCell.FormulaR1C1 = "=+SUM(R[-1]C[-2]:R[2494]C[-2])-1"
Range("E7").Select
ActiveWindow.SmallScroll Down:=-27
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Range("F5:F6").Select
Selection.Font.Bold = True
Range("F6").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Range("A1").Select
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
The third macro in my workbook is currently not working properly - if I just run it as is below, I get a "run time error 5 - invalid procedure call or argument" If I delete the sheet reference data for the first pivot, as I did with the macro listed above and re-run, I am only able to get the first pivot table to run successfully, while creating the second, I get a "run time error 9 subscript out of range" error (red highlight below.) I would also like this newly created sheet to be named "FFP Pivot." I apologize for the scroll length - I needed these counts to accomodate larger amounts of data as necessary :) Thanks so much for the kind assistance!!
Sub FFP()
'
' FFP Macro
'
'
Sheets.Add
ActiveWorkbook.Worksheets("CLSB").PivotTables("PivotTable11").PivotCache. _
CreatePivotTable TableDestination:="Sheet10!R3C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Sheet10").Select
Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Status")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Contract Type (Cntrct #) (Curr)")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Purchase Order Number" _
)
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Status").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Status")
.PivotItems("").Visible = False
.PivotItems("AWPA").Visible = False
.PivotItems("CLSD").Visible = False
.PivotItems("FNL").Visible = False
.PivotItems("HOLD").Visible = False
.PivotItems("INIT").Visible = False
.PivotItems("PEND").Visible = False
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Status"). _
EnableMultiplePageItems = True
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Contract Type (Cntrct #) (Curr)").CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Contract Type (Cntrct #) (Curr)")
.PivotItems("").Visible = False
.PivotItems("Cost Plus Contract").Visible = False
.PivotItems("Time & Mat Contract").Visible = False
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Contract Type (Cntrct #) (Curr)").EnableMultiplePageItems = True
Range("C5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-2]:RC[-2],RC[-2])"
Range("C5").Select
Selection.Copy
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 197
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 295
ActiveWindow.ScrollRow = 309
ActiveWindow.ScrollRow = 323
ActiveWindow.ScrollRow = 336
ActiveWindow.ScrollRow = 378
ActiveWindow.ScrollRow = 406
ActiveWindow.ScrollRow = 462
ActiveWindow.ScrollRow = 504
ActiveWindow.ScrollRow = 546
ActiveWindow.ScrollRow = 616
ActiveWindow.ScrollRow = 672
ActiveWindow.ScrollRow = 728
ActiveWindow.ScrollRow = 798
ActiveWindow.ScrollRow = 868
ActiveWindow.ScrollRow = 952
ActiveWindow.ScrollRow = 1035
ActiveWindow.ScrollRow = 1133
ActiveWindow.ScrollRow = 1231
ActiveWindow.ScrollRow = 1315
ActiveWindow.ScrollRow = 1385
ActiveWindow.ScrollRow = 1469
ActiveWindow.ScrollRow = 1553
ActiveWindow.ScrollRow = 1595
ActiveWindow.ScrollRow = 1650
ActiveWindow.ScrollRow = 1706
ActiveWindow.ScrollRow = 1748
ActiveWindow.ScrollRow = 1776
ActiveWindow.ScrollRow = 1818
ActiveWindow.ScrollRow = 1846
ActiveWindow.ScrollRow = 1874
ActiveWindow.ScrollRow = 1902
ActiveWindow.ScrollRow = 1916
ActiveWindow.ScrollRow = 1944
ActiveWindow.ScrollRow = 1958
ActiveWindow.ScrollRow = 1972
ActiveWindow.ScrollRow = 1986
ActiveWindow.ScrollRow = 2014
ActiveWindow.ScrollRow = 2042
ActiveWindow.ScrollRow = 2056
ActiveWindow.ScrollRow = 2084
ActiveWindow.ScrollRow = 2098
ActiveWindow.ScrollRow = 2112
ActiveWindow.ScrollRow = 2126
ActiveWindow.ScrollRow = 2140
ActiveWindow.ScrollRow = 2154
ActiveWindow.ScrollRow = 2168
ActiveWindow.ScrollRow = 2182
ActiveWindow.ScrollRow = 2196
ActiveWindow.ScrollRow = 2238
ActiveWindow.ScrollRow = 2279
ActiveWindow.ScrollRow = 2321
ActiveWindow.ScrollRow = 2377
ActiveWindow.ScrollRow = 2447
ActiveWindow.ScrollRow = 2489
ActiveWindow.ScrollRow = 2531
ActiveWindow.ScrollRow = 2601
ActiveWindow.ScrollRow = 2657
ActiveWindow.ScrollRow = 2741
ActiveWindow.ScrollRow = 2811
ActiveWindow.ScrollRow = 2908
ActiveWindow.ScrollRow = 2992
ActiveWindow.ScrollRow = 3048
ActiveWindow.ScrollRow = 3104
ActiveWindow.ScrollRow = 3132
ActiveWindow.ScrollRow = 3174
ActiveWindow.ScrollRow = 3216
ActiveWindow.ScrollRow = 3244
ActiveWindow.ScrollRow = 3272
ActiveWindow.ScrollRow = 3300
ActiveWindow.ScrollRow = 3328
ActiveWindow.ScrollRow = 3356
ActiveWindow.ScrollRow = 3384
ActiveWindow.ScrollRow = 3412
ActiveWindow.ScrollRow = 3426
ActiveWindow.ScrollRow = 3468
ActiveWindow.ScrollRow = 3496
ActiveWindow.ScrollRow = 3510
ActiveWindow.ScrollRow = 3538
ActiveWindow.ScrollRow = 3565
ActiveWindow.ScrollRow = 3579
ActiveWindow.ScrollRow = 3607
ActiveWindow.ScrollRow = 3621
ActiveWindow.ScrollRow = 3635
ActiveWindow.ScrollRow = 3663
ActiveWindow.ScrollRow = 3705
ActiveWindow.ScrollRow = 3719
ActiveWindow.ScrollRow = 3747
ActiveWindow.ScrollRow = 3775
ActiveWindow.ScrollRow = 3817
ActiveWindow.ScrollRow = 3845
ActiveWindow.ScrollRow = 3859
ActiveWindow.ScrollRow = 3887
ActiveWindow.ScrollRow = 3915
ActiveWindow.ScrollRow = 3929
ActiveWindow.ScrollRow = 3943
ActiveWindow.ScrollRow = 3957
ActiveWindow.ScrollRow = 3971
ActiveWindow.ScrollRow = 3985
ActiveWindow.ScrollRow = 3999
ActiveWindow.ScrollRow = 4013
ActiveWindow.ScrollRow = 4027
ActiveWindow.ScrollRow = 4055
ActiveWindow.ScrollRow = 4069
ActiveWindow.ScrollRow = 4083
ActiveWindow.ScrollRow = 4097
ActiveWindow.ScrollRow = 4111
ActiveWindow.ScrollRow = 4125
ActiveWindow.ScrollRow = 4139
ActiveWindow.ScrollRow = 4153
ActiveWindow.ScrollRow = 4167
ActiveWindow.ScrollRow = 4181
ActiveWindow.ScrollRow = 4195
ActiveWindow.ScrollRow = 4208
ActiveWindow.ScrollRow = 4222
ActiveWindow.ScrollRow = 4236
ActiveWindow.ScrollRow = 4250
ActiveWindow.ScrollRow = 4278
ActiveWindow.ScrollRow = 4292
ActiveWindow.ScrollRow = 4306
ActiveWindow.ScrollRow = 4320
ActiveWindow.ScrollRow = 4334
ActiveWindow.ScrollRow = 4348
ActiveWindow.ScrollRow = 4362
ActiveWindow.ScrollRow = 4376
ActiveWindow.ScrollRow = 4390
ActiveWindow.ScrollRow = 4404
ActiveWindow.ScrollRow = 4418
ActiveWindow.ScrollRow = 4432
ActiveWindow.ScrollRow = 4446
ActiveWindow.ScrollRow = 4460
ActiveWindow.ScrollRow = 4474
ActiveWindow.ScrollRow = 4488
ActiveWindow.ScrollRow = 4502
ActiveWindow.ScrollRow = 4516
ActiveWindow.ScrollRow = 4530
ActiveWindow.ScrollRow = 4544
ActiveWindow.ScrollRow = 4558
ActiveWindow.ScrollRow = 4572
ActiveWindow.ScrollRow = 4586
ActiveWindow.ScrollRow = 4600
ActiveWindow.ScrollRow = 4614
ActiveWindow.ScrollRow = 4628
ActiveWindow.ScrollRow = 4642
ActiveWindow.ScrollRow = 4656
ActiveWindow.ScrollRow = 4670
ActiveWindow.ScrollRow = 4684
ActiveWindow.ScrollRow = 4698
ActiveWindow.ScrollRow = 4712
ActiveWindow.ScrollRow = 4726
ActiveWindow.ScrollRow = 4740
ActiveWindow.ScrollRow = 4754
ActiveWindow.ScrollRow = 4768
ActiveWindow.ScrollRow = 4782
ActiveWindow.ScrollRow = 4796
ActiveWindow.ScrollRow = 4810
ActiveWindow.ScrollRow = 4824
ActiveWindow.ScrollRow = 4838
ActiveWindow.ScrollRow = 4851
ActiveWindow.ScrollRow = 4865
ActiveWindow.ScrollRow = 4879
ActiveWindow.ScrollRow = 4893
ActiveWindow.ScrollRow = 4907
ActiveWindow.ScrollRow = 4921
ActiveWindow.ScrollRow = 4935
ActiveWindow.ScrollRow = 4949
ActiveWindow.ScrollRow = 4963
ActiveWindow.ScrollRow = 4977
ActiveWindow.ScrollRow = 4991
ActiveWindow.ScrollRow = 5005
ActiveWindow.ScrollRow = 5019
ActiveWindow.ScrollRow = 5033
ActiveWindow.ScrollRow = 5047
ActiveWindow.ScrollRow = 5075
ActiveWindow.ScrollRow = 5089
ActiveWindow.ScrollRow = 5103
ActiveWindow.ScrollRow = 5117
ActiveWindow.ScrollRow = 5131
ActiveWindow.ScrollRow = 5145
ActiveWindow.ScrollRow = 5159
ActiveWindow.ScrollRow = 5173
ActiveWindow.ScrollRow = 5187
ActiveWindow.ScrollRow = 5201
ActiveWindow.ScrollRow = 5215
ActiveWindow.ScrollRow = 5229
ActiveWindow.ScrollRow = 5243
ActiveWindow.ScrollRow = 5257
ActiveWindow.ScrollRow = 5271
ActiveWindow.ScrollRow = 5285
ActiveWindow.ScrollRow = 5327
ActiveWindow.ScrollRow = 5355
ActiveWindow.ScrollRow = 5383
ActiveWindow.ScrollRow = 5411
ActiveWindow.ScrollRow = 5439
ActiveWindow.ScrollRow = 5453
ActiveWindow.ScrollRow = 5467
ActiveWindow.ScrollRow = 5481
Range("C5:C5500").Select
ActiveSheet.Paste
Range("D5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CLSB"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:R[5495]C[-2])-1"
Range("E6").Select
ActiveWindow.SmallScroll Down:=-9
Range("E5").Select
Selection.Font.Bold = True
Range("D5:E5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Range("F1").Select
ActiveWorkbook.Worksheets("Sheet10").PivotTables("PivotTable2").PivotCache. _
CreatePivotTable TableDestination:="Sheet10!R1C6", TableName:="PivotTable3" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Sheet10").Select
Cells(1, 6).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Status")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Contract Type (Cntrct #) (Curr)")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Purchase Order Number" _
)
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields("Status").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Status")
.PivotItems("").Visible = False
.PivotItems("AWPA").Visible = False
.PivotItems("CLSB").Visible = False
.PivotItems("CLSD").Visible = False
.PivotItems("FNL").Visible = False
.PivotItems("HOLD").Visible = False
.PivotItems("INIT").Visible = False
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields("Status"). _
EnableMultiplePageItems = True
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Contract Type (Cntrct #) (Curr)").CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Contract Type (Cntrct #) (Curr)")
.PivotItems("").Visible = False
.PivotItems("Cost Plus Contract").Visible = False
.PivotItems("Time & Mat Contract").Visible = False
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Contract Type (Cntrct #) (Curr)").EnableMultiplePageItems = True
Range("H5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-2]:RC[-2],RC[-2])"
Range("H5").Select
Selection.Copy
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 183
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 281
ActiveWindow.ScrollRow = 309
ActiveWindow.ScrollRow = 336
ActiveWindow.ScrollRow = 350
ActiveWindow.ScrollRow = 364
ActiveWindow.ScrollRow = 392
ActiveWindow.ScrollRow = 420
ActiveWindow.ScrollRow = 434
ActiveWindow.ScrollRow = 462
ActiveWindow.ScrollRow = 490
ActiveWindow.ScrollRow = 518
ActiveWindow.ScrollRow = 532
ActiveWindow.ScrollRow = 560
ActiveWindow.ScrollRow = 588
ActiveWindow.ScrollRow = 602
ActiveWindow.ScrollRow = 630
ActiveWindow.ScrollRow = 658
ActiveWindow.ScrollRow = 672
ActiveWindow.ScrollRow = 686
ActiveWindow.ScrollRow = 700
ActiveWindow.ScrollRow = 714
ActiveWindow.ScrollRow = 728
ActiveWindow.ScrollRow = 742
ActiveWindow.ScrollRow = 756
ActiveWindow.ScrollRow = 770
ActiveWindow.ScrollRow = 784
ActiveWindow.ScrollRow = 798
ActiveWindow.ScrollRow = 812
ActiveWindow.ScrollRow = 826
ActiveWindow.ScrollRow = 840
ActiveWindow.ScrollRow = 854
ActiveWindow.ScrollRow = 882
ActiveWindow.ScrollRow = 896
ActiveWindow.ScrollRow = 910
ActiveWindow.ScrollRow = 924
ActiveWindow.ScrollRow = 938
ActiveWindow.ScrollRow = 952
ActiveWindow.ScrollRow = 938
ActiveWindow.ScrollRow = 896
ActiveWindow.ScrollRow = 868
ActiveWindow.ScrollRow = 840
ActiveWindow.ScrollRow = 812
ActiveWindow.ScrollRow = 784
ActiveWindow.ScrollRow = 770
ActiveWindow.ScrollRow = 756
ActiveWindow.ScrollRow = 742
ActiveWindow.ScrollRow = 728
ActiveWindow.ScrollRow = 742
ActiveWindow.ScrollRow = 756
ActiveWindow.ScrollRow = 770
ActiveWindow.ScrollRow = 784
ActiveWindow.ScrollRow = 798
ActiveWindow.ScrollRow = 812
ActiveWindow.ScrollRow = 826
ActiveWindow.ScrollRow = 840
ActiveWindow.ScrollRow = 854
ActiveWindow.ScrollRow = 868
ActiveWindow.ScrollRow = 882
ActiveWindow.ScrollRow = 896
ActiveWindow.ScrollRow = 910
ActiveWindow.ScrollRow = 924
ActiveWindow.ScrollRow = 952
ActiveWindow.ScrollRow = 966
ActiveWindow.ScrollRow = 979
ActiveWindow.ScrollRow = 1007
ActiveWindow.ScrollRow = 1021
ActiveWindow.ScrollRow = 1035
ActiveWindow.ScrollRow = 1049
ActiveWindow.ScrollRow = 1063
ActiveWindow.ScrollRow = 1077
ActiveWindow.ScrollRow = 1091
ActiveWindow.ScrollRow = 1105
ActiveWindow.ScrollRow = 1119
ActiveWindow.ScrollRow = 1133
ActiveWindow.ScrollRow = 1147
ActiveWindow.ScrollRow = 1161
ActiveWindow.ScrollRow = 1175
ActiveWindow.ScrollRow = 1189
ActiveWindow.ScrollRow = 1203
ActiveWindow.ScrollRow = 1231
ActiveWindow.ScrollRow = 1245
ActiveWindow.ScrollRow = 1259
ActiveWindow.ScrollRow = 1287
ActiveWindow.ScrollRow = 1315
ActiveWindow.ScrollRow = 1343
ActiveWindow.ScrollRow = 1357
ActiveWindow.ScrollRow = 1385
ActiveWindow.ScrollRow = 1399
ActiveWindow.ScrollRow = 1413
ActiveWindow.ScrollRow = 1427
ActiveWindow.ScrollRow = 1441
ActiveWindow.ScrollRow = 1455
ActiveWindow.ScrollRow = 1483
ActiveWindow.ScrollRow = 1511
ActiveWindow.ScrollRow = 1525
ActiveWindow.ScrollRow = 1567
ActiveWindow.ScrollRow = 1609
ActiveWindow.ScrollRow = 1650
ActiveWindow.ScrollRow = 1692
ActiveWindow.ScrollRow = 1720
ActiveWindow.ScrollRow = 1748
ActiveWindow.ScrollRow = 1776
ActiveWindow.ScrollRow = 1804
ActiveWindow.ScrollRow = 1860
ActiveWindow.ScrollRow = 1888
ActiveWindow.ScrollRow = 1930
ActiveWindow.ScrollRow = 1972
ActiveWindow.ScrollRow = 2028
ActiveWindow.ScrollRow = 2070
ActiveWindow.ScrollRow = 2098
ActiveWindow.ScrollRow = 2126
ActiveWindow.ScrollRow = 2168
ActiveWindow.ScrollRow = 2182
ActiveWindow.ScrollRow = 2196
ActiveWindow.ScrollRow = 2224
ActiveWindow.ScrollRow = 2238
ActiveWindow.ScrollRow = 2265
ActiveWindow.ScrollRow = 2293
ActiveWindow.ScrollRow = 2321
ActiveWindow.ScrollRow = 2335
ActiveWindow.ScrollRow = 2363
ActiveWindow.ScrollRow = 2391
ActiveWindow.ScrollRow = 2419
ActiveWindow.ScrollRow = 2433
ActiveWindow.ScrollRow = 2447
ActiveWindow.ScrollRow = 2461
ActiveWindow.ScrollRow = 2475
ActiveWindow.ScrollRow = 2489
ActiveWindow.ScrollRow = 2503
ActiveWindow.ScrollRow = 2517
ActiveWindow.ScrollRow = 2545
ActiveWindow.ScrollRow = 2573
ActiveWindow.ScrollRow = 2601
ActiveWindow.ScrollRow = 2629
ActiveWindow.ScrollRow = 2671
ActiveWindow.ScrollRow = 2699
ActiveWindow.ScrollRow = 2727
ActiveWindow.ScrollRow = 2769
ActiveWindow.ScrollRow = 2797
ActiveWindow.ScrollRow = 2825
ActiveWindow.ScrollRow = 2839
ActiveWindow.ScrollRow = 2881
ActiveWindow.ScrollRow = 2895
ActiveWindow.ScrollRow = 2908
ActiveWindow.ScrollRow = 2936
ActiveWindow.ScrollRow = 2950
ActiveWindow.ScrollRow = 2964
ActiveWindow.ScrollRow = 2978
ActiveWindow.ScrollRow = 2992
ActiveWindow.ScrollRow = 3006
ActiveWindow.ScrollRow = 3020
ActiveWindow.ScrollRow = 3048
ActiveWindow.ScrollRow = 3062
ActiveWindow.ScrollRow = 3076
ActiveWindow.ScrollRow = 3090
ActiveWindow.ScrollRow = 3104
ActiveWindow.ScrollRow = 3118
ActiveWindow.ScrollRow = 3146
ActiveWindow.ScrollRow = 3160
ActiveWindow.ScrollRow = 3174
ActiveWindow.ScrollRow = 3202
ActiveWindow.ScrollRow = 3216
ActiveWindow.ScrollRow = 3230
ActiveWindow.ScrollRow = 3244
ActiveWindow.ScrollRow = 3258
ActiveWindow.ScrollRow = 3272
ActiveWindow.ScrollRow = 3286
ActiveWindow.ScrollRow = 3300
ActiveWindow.ScrollRow = 3314
ActiveWindow.ScrollRow = 3328
ActiveWindow.ScrollRow = 3356
ActiveWindow.ScrollRow = 3384
ActiveWindow.ScrollRow = 3398
ActiveWindow.ScrollRow = 3412
ActiveWindow.ScrollRow = 3440
ActiveWindow.ScrollRow = 3454
ActiveWindow.ScrollRow = 3468
ActiveWindow.ScrollRow = 3482
ActiveWindow.ScrollRow = 3496
ActiveWindow.ScrollRow = 3510
ActiveWindow.ScrollRow = 3524
ActiveWindow.ScrollRow = 3538
ActiveWindow.ScrollRow = 3551
ActiveWindow.ScrollRow = 3565
ActiveWindow.ScrollRow = 3579
ActiveWindow.ScrollRow = 3593
ActiveWindow.ScrollRow = 3607
ActiveWindow.ScrollRow = 3621
ActiveWindow.ScrollRow = 3649
ActiveWindow.ScrollRow = 3677
ActiveWindow.ScrollRow = 3691
ActiveWindow.ScrollRow = 3705
ActiveWindow.ScrollRow = 3719
ActiveWindow.ScrollRow = 3733
ActiveWindow.ScrollRow = 3747
ActiveWindow.ScrollRow = 3761
ActiveWindow.ScrollRow = 3775
ActiveWindow.ScrollRow = 3803
ActiveWindow.ScrollRow = 3817
ActiveWindow.ScrollRow = 3831
ActiveWindow.ScrollRow = 3845
ActiveWindow.ScrollRow = 3873
ActiveWindow.ScrollRow = 3887
ActiveWindow.ScrollRow = 3901
ActiveWindow.ScrollRow = 3915
ActiveWindow.ScrollRow = 3929
ActiveWindow.ScrollRow = 3943
ActiveWindow.ScrollRow = 3957
ActiveWindow.ScrollRow = 3971
ActiveWindow.ScrollRow = 3985
ActiveWindow.ScrollRow = 3999
ActiveWindow.ScrollRow = 4013
ActiveWindow.ScrollRow = 4027
ActiveWindow.ScrollRow = 4041
ActiveWindow.ScrollRow = 4055
ActiveWindow.ScrollRow = 4069
ActiveWindow.ScrollRow = 4083
ActiveWindow.ScrollRow = 4097
ActiveWindow.ScrollRow = 4111
ActiveWindow.ScrollRow = 4125
ActiveWindow.ScrollRow = 4139
ActiveWindow.ScrollRow = 4153
ActiveWindow.ScrollRow = 4167
ActiveWindow.ScrollRow = 4181
ActiveWindow.ScrollRow = 4195
ActiveWindow.ScrollRow = 4208
ActiveWindow.ScrollRow = 4222
ActiveWindow.ScrollRow = 4236
ActiveWindow.ScrollRow = 4250
ActiveWindow.ScrollRow = 4264
ActiveWindow.ScrollRow = 4278
ActiveWindow.ScrollRow = 4292
ActiveWindow.ScrollRow = 4306
ActiveWindow.ScrollRow = 4320
ActiveWindow.ScrollRow = 4334
ActiveWindow.ScrollRow = 4362
ActiveWindow.ScrollRow = 4376
ActiveWindow.ScrollRow = 4390
ActiveWindow.ScrollRow = 4404
ActiveWindow.ScrollRow = 4418
ActiveWindow.ScrollRow = 4432
ActiveWindow.ScrollRow = 4460
ActiveWindow.ScrollRow = 4474
ActiveWindow.ScrollRow = 4488
ActiveWindow.ScrollRow = 4502
ActiveWindow.ScrollRow = 4530
ActiveWindow.ScrollRow = 4544
ActiveWindow.ScrollRow = 4572
ActiveWindow.ScrollRow = 4600
ActiveWindow.ScrollRow = 4614
ActiveWindow.ScrollRow = 4642
ActiveWindow.ScrollRow = 4656
ActiveWindow.ScrollRow = 4670
ActiveWindow.ScrollRow = 4698
ActiveWindow.ScrollRow = 4712
ActiveWindow.ScrollRow = 4726
ActiveWindow.ScrollRow = 4754
ActiveWindow.ScrollRow = 4768
ActiveWindow.ScrollRow = 4782
ActiveWindow.ScrollRow = 4796
ActiveWindow.ScrollRow = 4810
ActiveWindow.ScrollRow = 4838
ActiveWindow.ScrollRow = 4851
ActiveWindow.ScrollRow = 4865
ActiveWindow.ScrollRow = 4879
ActiveWindow.ScrollRow = 4893
ActiveWindow.ScrollRow = 4907
ActiveWindow.ScrollRow = 4921
ActiveWindow.ScrollRow = 4935
ActiveWindow.ScrollRow = 4949
ActiveWindow.ScrollRow = 4963
ActiveWindow.ScrollRow = 4977
ActiveWindow.ScrollRow = 4991
ActiveWindow.ScrollRow = 5005
ActiveWindow.ScrollRow = 5019
ActiveWindow.ScrollRow = 5033
ActiveWindow.ScrollRow = 5047
ActiveWindow.ScrollRow = 5061
ActiveWindow.ScrollRow = 5075
ActiveWindow.ScrollRow = 5089
ActiveWindow.ScrollRow = 5103
ActiveWindow.ScrollRow = 5117
ActiveWindow.ScrollRow = 5131
ActiveWindow.ScrollRow = 5145
ActiveWindow.ScrollRow = 5159
ActiveWindow.ScrollRow = 5173
ActiveWindow.ScrollRow = 5187
ActiveWindow.ScrollRow = 5201
ActiveWindow.ScrollRow = 5215
ActiveWindow.ScrollRow = 5229
ActiveWindow.ScrollRow = 5243
ActiveWindow.ScrollRow = 5257
ActiveWindow.ScrollRow = 5271
ActiveWindow.ScrollRow = 5299
ActiveWindow.ScrollRow = 5313
ActiveWindow.ScrollRow = 5327
ActiveWindow.ScrollRow = 5355
ActiveWindow.ScrollRow = 5369
ActiveWindow.ScrollRow = 5383
ActiveWindow.ScrollRow = 5411
ActiveWindow.ScrollRow = 5425
ActiveWindow.ScrollRow = 5439
ActiveWindow.ScrollRow = 5453
ActiveWindow.ScrollRow = 5467
ActiveWindow.ScrollRow = 5481
ActiveWindow.ScrollRow = 5494
ActiveWindow.ScrollRow = 5508
ActiveWindow.ScrollRow = 5522
ActiveWindow.ScrollRow = 5536
ActiveWindow.ScrollRow = 5550
ActiveWindow.ScrollRow = 5564
ActiveWindow.ScrollRow = 5578
ActiveWindow.ScrollRow = 5592
ActiveWindow.ScrollRow = 5606
ActiveWindow.ScrollRow = 5620
ActiveWindow.ScrollRow = 5634
ActiveWindow.ScrollRow = 5620
ActiveWindow.ScrollRow = 5606
ActiveWindow.ScrollRow = 5592
ActiveWindow.ScrollRow = 5578
ActiveWindow.ScrollRow = 5564
ActiveWindow.ScrollRow = 5550
ActiveWindow.ScrollRow = 5536
ActiveWindow.ScrollRow = 5522
ActiveWindow.ScrollRow = 5508
ActiveWindow.ScrollRow = 5494
ActiveWindow.ScrollRow = 5481
Range("H5:H5500").Select
ActiveSheet.Paste
Range("I5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "PEND"
Range("J5").Select
ActiveCell.FormulaR1C1 = "=+SUM(RC[-2]:R[5495]C[-2])-1"
Range("I5:J5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J5").Select
ActiveCell.FormulaR1C1 = "=+SUM(RC[-2]:R[5495]C[-2])-1"
Range("J5").Select
Selection.Font.Bold = True
Range("K1").Select
ActiveWorkbook.Worksheets("Sheet10").PivotTables("PivotTable3").PivotCache. _
CreatePivotTable TableDestination:="Sheet10!R1C11", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion14
Sheets("Sheet10").Select
Cells(1, 11).Select
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Status")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"Contract Type (Cntrct #) (Curr)")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Purchase Order Number" _
)
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").PivotFields("Status").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Status")
.PivotItems("").Visible = False
.PivotItems("AWPA").Visible = False
.PivotItems("CLSB").Visible = False
.PivotItems("FNL").Visible = False
.PivotItems("HOLD").Visible = False
.PivotItems("INIT").Visible = False
.PivotItems("PEND").Visible = False
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable4").PivotFields("Status"). _
EnableMultiplePageItems = True
ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"Contract Type (Cntrct #) (Curr)").CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"Contract Type (Cntrct #) (Curr)")
.PivotItems("").Visible = False
.PivotItems("Cost Plus Contract").Visible = False
.PivotItems("Time & Mat Contract").Visible = False
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"Contract Type (Cntrct #) (Curr)").EnableMultiplePageItems = True
Range("M5").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-2]:RC[-2],RC[-2])"
Range("M5").Select
Selection.Copy
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 183
ActiveWindow.ScrollRow = 309
ActiveWindow.ScrollRow = 378
ActiveWindow.ScrollRow = 434
ActiveWindow.ScrollRow = 476
ActiveWindow.ScrollRow = 532
ActiveWindow.ScrollRow = 588
ActiveWindow.ScrollRow = 672
ActiveWindow.ScrollRow = 728
ActiveWindow.ScrollRow = 798
ActiveWindow.ScrollRow = 854
ActiveWindow.ScrollRow = 910
ActiveWindow.ScrollRow = 952
ActiveWindow.ScrollRow = 979
ActiveWindow.ScrollRow = 1021
ActiveWindow.ScrollRow = 1063
ActiveWindow.ScrollRow = 1091
ActiveWindow.ScrollRow = 1133
ActiveWindow.ScrollRow = 1161
ActiveWindow.ScrollRow = 1203
ActiveWindow.ScrollRow = 1259
ActiveWindow.ScrollRow = 1301
ActiveWindow.ScrollRow = 1343
ActiveWindow.ScrollRow = 1385
ActiveWindow.ScrollRow = 1427
ActiveWindow.ScrollRow = 1469
ActiveWindow.ScrollRow = 1497
ActiveWindow.ScrollRow = 1539
ActiveWindow.ScrollRow = 1595
ActiveWindow.ScrollRow = 1636
ActiveWindow.ScrollRow = 1678
ActiveWindow.ScrollRow = 1734
ActiveWindow.ScrollRow = 1804
ActiveWindow.ScrollRow = 1846
ActiveWindow.ScrollRow = 1874
ActiveWindow.ScrollRow = 1902
ActiveWindow.ScrollRow = 1930
ActiveWindow.ScrollRow = 1944
ActiveWindow.ScrollRow = 1958
ActiveWindow.ScrollRow = 1986
ActiveWindow.ScrollRow = 2014
ActiveWindow.ScrollRow = 2056
ActiveWindow.ScrollRow = 2140
ActiveWindow.ScrollRow = 2182
ActiveWindow.ScrollRow = 2224
ActiveWindow.ScrollRow = 2252
ActiveWindow.ScrollRow = 2293
ActiveWindow.ScrollRow = 2335
ActiveWindow.ScrollRow = 2377
ActiveWindow.ScrollRow = 2419
ActiveWindow.ScrollRow = 2475
ActiveWindow.ScrollRow = 2517
ActiveWindow.ScrollRow = 2545
ActiveWindow.ScrollRow = 2587
ActiveWindow.ScrollRow = 2629
ActiveWindow.ScrollRow = 2657
ActiveWindow.ScrollRow = 2699
ActiveWindow.ScrollRow = 2727
ActiveWindow.ScrollRow = 2755
ActiveWindow.ScrollRow = 2783
ActiveWindow.ScrollRow = 2825
ActiveWindow.ScrollRow = 2853
ActiveWindow.ScrollRow = 2881
ActiveWindow.ScrollRow = 2895
ActiveWindow.ScrollRow = 2922
ActiveWindow.ScrollRow = 2936
ActiveWindow.ScrollRow = 2964
ActiveWindow.ScrollRow = 2978
ActiveWindow.ScrollRow = 2992
ActiveWindow.ScrollRow = 3006
ActiveWindow.ScrollRow = 3020
ActiveWindow.ScrollRow = 3034
ActiveWindow.ScrollRow = 3048
ActiveWindow.ScrollRow = 3062
ActiveWindow.ScrollRow = 3090
ActiveWindow.ScrollRow = 3104
ActiveWindow.ScrollRow = 3132
ActiveWindow.ScrollRow = 3160
ActiveWindow.ScrollRow = 3174
ActiveWindow.ScrollRow = 3202
ActiveWindow.ScrollRow = 3230
ActiveWindow.ScrollRow = 3244
ActiveWindow.ScrollRow = 3258
ActiveWindow.ScrollRow = 3286
ActiveWindow.ScrollRow = 3300
ActiveWindow.ScrollRow = 3314
ActiveWindow.ScrollRow = 3328
ActiveWindow.ScrollRow = 3342
ActiveWindow.ScrollRow = 3356
ActiveWindow.ScrollRow = 3370
ActiveWindow.ScrollRow = 3398
ActiveWindow.ScrollRow = 3440
ActiveWindow.ScrollRow = 3468
ActiveWindow.ScrollRow = 3524
ActiveWindow.ScrollRow = 3551
ActiveWindow.ScrollRow = 3565
ActiveWindow.ScrollRow = 3579
ActiveWindow.ScrollRow = 3593
ActiveWindow.ScrollRow = 3607
ActiveWindow.ScrollRow = 3621
ActiveWindow.ScrollRow = 3635
ActiveWindow.ScrollRow = 3649
ActiveWindow.ScrollRow = 3663
ActiveWindow.ScrollRow = 3677
ActiveWindow.ScrollRow = 3691
ActiveWindow.ScrollRow = 3705
ActiveWindow.ScrollRow = 3733
ActiveWindow.ScrollRow = 3747
ActiveWindow.ScrollRow = 3761
ActiveWindow.ScrollRow = 3775
ActiveWindow.ScrollRow = 3789
ActiveWindow.ScrollRow = 3803
ActiveWindow.ScrollRow = 3817
ActiveWindow.ScrollRow = 3831
ActiveWindow.ScrollRow = 3859
ActiveWindow.ScrollRow = 3873
ActiveWindow.ScrollRow = 3887
ActiveWindow.ScrollRow = 3901
ActiveWindow.ScrollRow = 3915
ActiveWindow.ScrollRow = 3929
ActiveWindow.ScrollRow = 3943
ActiveWindow.ScrollRow = 3957
ActiveWindow.ScrollRow = 3971
ActiveWindow.ScrollRow = 3985
ActiveWindow.ScrollRow = 3999
ActiveWindow.ScrollRow = 4027
ActiveWindow.ScrollRow = 4041
ActiveWindow.ScrollRow = 4055
ActiveWindow.ScrollRow = 4069
ActiveWindow.ScrollRow = 4083
ActiveWindow.ScrollRow = 4097
ActiveWindow.ScrollRow = 4111
ActiveWindow.ScrollRow = 4125
ActiveWindow.ScrollRow = 4139
ActiveWindow.ScrollRow = 4153
ActiveWindow.ScrollRow = 4167
ActiveWindow.ScrollRow = 4181
ActiveWindow.ScrollRow = 4195
ActiveWindow.ScrollRow = 4208
ActiveWindow.ScrollRow = 4222
ActiveWindow.ScrollRow = 4236
ActiveWindow.ScrollRow = 4250
ActiveWindow.ScrollRow = 4264
ActiveWindow.ScrollRow = 4278
ActiveWindow.ScrollRow = 4292
ActiveWindow.ScrollRow = 4306
ActiveWindow.ScrollRow = 4320
ActiveWindow.ScrollRow = 4334
ActiveWindow.ScrollRow = 4348
ActiveWindow.ScrollRow = 4362
ActiveWindow.ScrollRow = 4376
ActiveWindow.ScrollRow = 4390
ActiveWindow.ScrollRow = 4404
ActiveWindow.ScrollRow = 4418
ActiveWindow.ScrollRow = 4432
ActiveWindow.ScrollRow = 4446
ActiveWindow.ScrollRow = 4460
ActiveWindow.ScrollRow = 4474
ActiveWindow.ScrollRow = 4488
ActiveWindow.ScrollRow = 4502
ActiveWindow.ScrollRow = 4516
ActiveWindow.ScrollRow = 4530
ActiveWindow.ScrollRow = 4544
ActiveWindow.ScrollRow = 4558
ActiveWindow.ScrollRow = 4572
ActiveWindow.ScrollRow = 4586
ActiveWindow.ScrollRow = 4600
ActiveWindow.ScrollRow = 4614
ActiveWindow.ScrollRow = 4628
ActiveWindow.ScrollRow = 4642
ActiveWindow.ScrollRow = 4670
ActiveWindow.ScrollRow = 4684
ActiveWindow.ScrollRow = 4712
ActiveWindow.ScrollRow = 4726
ActiveWindow.ScrollRow = 4740
ActiveWindow.ScrollRow = 4754
ActiveWindow.ScrollRow = 4768
ActiveWindow.ScrollRow = 4782
ActiveWindow.ScrollRow = 4810
ActiveWindow.ScrollRow = 4824
ActiveWindow.ScrollRow = 4851
ActiveWindow.ScrollRow = 4865
ActiveWindow.ScrollRow = 4893
ActiveWindow.ScrollRow = 4907
ActiveWindow.ScrollRow = 4921
ActiveWindow.ScrollRow = 4935
ActiveWindow.ScrollRow = 4949
ActiveWindow.ScrollRow = 4963
ActiveWindow.ScrollRow = 4977
ActiveWindow.ScrollRow = 4991
ActiveWindow.ScrollRow = 5005
ActiveWindow.ScrollRow = 5019
ActiveWindow.ScrollRow = 5033
ActiveWindow.ScrollRow = 5047
ActiveWindow.ScrollRow = 5061
ActiveWindow.ScrollRow = 5075
ActiveWindow.ScrollRow = 5089
ActiveWindow.ScrollRow = 5103
ActiveWindow.ScrollRow = 5117
ActiveWindow.ScrollRow = 5131
ActiveWindow.ScrollRow = 5145
ActiveWindow.ScrollRow = 5159
ActiveWindow.ScrollRow = 5173
ActiveWindow.ScrollRow = 5187
ActiveWindow.ScrollRow = 5215
ActiveWindow.ScrollRow = 5229
ActiveWindow.ScrollRow = 5271
ActiveWindow.ScrollRow = 5299
ActiveWindow.ScrollRow = 5313
ActiveWindow.ScrollRow = 5327
ActiveWindow.ScrollRow = 5341
ActiveWindow.ScrollRow = 5355
ActiveWindow.ScrollRow = 5369
ActiveWindow.ScrollRow = 5383
ActiveWindow.ScrollRow = 5397
ActiveWindow.ScrollRow = 5411
ActiveWindow.ScrollRow = 5425
ActiveWindow.ScrollRow = 5439
ActiveWindow.ScrollRow = 5453
ActiveWindow.ScrollRow = 5467
ActiveWindow.ScrollRow = 5481
ActiveWindow.ScrollRow = 5494
ActiveWindow.ScrollRow = 5508
ActiveWindow.ScrollRow = 5522
ActiveWindow.ScrollRow = 5536
ActiveWindow.ScrollRow = 5550
ActiveWindow.ScrollRow = 5564
ActiveWindow.ScrollRow = 5578
ActiveWindow.ScrollRow = 5592
ActiveWindow.ScrollRow = 5578
ActiveWindow.ScrollRow = 5564
ActiveWindow.ScrollRow = 5550
ActiveWindow.ScrollRow = 5536
ActiveWindow.ScrollRow = 5522
ActiveWindow.ScrollRow = 5508
ActiveWindow.ScrollRow = 5494
ActiveWindow.ScrollRow = 5481
ActiveWindow.ScrollRow = 5467
Range("M5:M5500").Select
ActiveSheet.Paste
Range("N5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "CLSD"
Range("O5").Select
ActiveCell.FormulaR1C1 = "=+SUM(RC[-2]:R[5495]C[-2])-4452"
Range("N5:O5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("O5").Select
Selection.Font.Bold = True
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Columns("H:H").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select
ActiveWorkbook.ShowPivotTableFieldList = False
End SubThanks again,
Sam
Last edited by a moderator:
