PLEASE HELP...How do I update a pivot table with VBA?

M

marthasanchez

I created a macro to run a SQL and return the data, then in excel there are
calculations being performed. Based on these calculations, I create a pivot
table. How can I have the pivot table update automatically everytime I run
the report?
This is what I have so far...........

ActiveSheet.Unprotect
Rows("7:7").Select
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=4
Selection.AutoFilter Field:=16, Criteria1:="<0", Operator:=xlAnd
Cells.Select
Range("E1").Activate
Selection.Copy
Sheets("OOS").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Buttons.Add(192.75, 26.25, 105.75, 22.5).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.ScrollRow = 1
ActiveWindow.LargeScroll ToRight:=-1
Sheets("ARO").Select
Rows("7:7").Select
Range("E7").Activate
Selection.AutoFilter Field:=16, Criteria1:=">0", Operator:=xlAnd
Cells.Select
Range("E1").Activate
Selection.Copy
Sheets("d").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Buttons.Add(192.75, 26.25, 105.75, 22.5).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-3
Sheets("ARO").Select
Rows("7:7").Select
Range("E7").Activate
Selection.AutoFilter
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.ScrollRow = 1
Range("B7").Select
Sheets("OOS").Select
Rows("7:7").Select
ActiveSheet.Shapes("Button 1").Select
Selection.ShapeRange.ZOrder msoSendToBack
ActiveSheet.Shapes("Button 1").Select
ActiveWindow.SmallScroll ToRight:=2
Selection.Characters.Text = "DO not Run"
With Selection.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.ShapeRange.ScaleWidth 1.02, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.97, msoFalse, msoScaleFromBottomRight
Selection.ShapeRange.ScaleWidth 1.2, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.IncrementLeft 2136#
Selection.ShapeRange.IncrementTop -21.75
ActiveWindow.LargeScroll ToRight:=-3
ActiveWindow.SmallScroll Down:=-15
Sheets("ARO").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets("OOS").Select
ActiveWindow.SmallScroll Down:=-3
Cells.Select
Selection.Copy
Sheets("Pivot").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Buttons.Add(2328.75, 5.25, 129.75, 21.75).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Rows("1:6").Select
Range("A6").Activate
Selection.Delete Shift:=xlUp
Range("B2").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Pivot!R1C2:R122C16").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="UW", _
ColumnFields:="Workbasket"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("OOS Days")
.Orientation = xlDataField
.Caption = "Count of OOS Days"
.Function = xlCount
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Columns("A:A").ColumnWidth = 9.43
Columns("B:B").ColumnWidth = 12.14
Columns("C:C").ColumnWidth = 13
Range("B4").Select

ActiveSheet.PivotTables("PivotTable1").PivotFields("Workbasket").PivotItems( _
"Endorsements").Caption = "Endr"
Range("C1").Select
Columns("B:B").ColumnWidth = 4.29
Columns("C:C").ColumnWidth = 12.86
Range("D4").Select

ActiveSheet.PivotTables("PivotTable1").PivotFields("Workbasket").PivotItems( _
"Loss Prevention/Benefits").Caption = "L/P"
Range("D5").Select
Columns("D:D").ColumnWidth = 3.29
Columns("E:E").ColumnWidth = 12.14
Range("E4").Select

ActiveSheet.PivotTables("PivotTable1").PivotFields("Workbasket").PivotItems( _
"New Business").Caption = "NB"
Range("E5").Select
Columns("E:E").ColumnWidth = 3.57
Columns("F:F").ColumnWidth = 10.43
Columns("G:G").ColumnWidth = 12.71
Columns("H:H").ColumnWidth = 8.14
Columns("I:I").ColumnWidth = 4.29
Columns("J:J").ColumnWidth = 4.57
Range("B4:K15").Select
Range("K15").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("B15:K15").Select
Range("K15").Activate
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B15:K15")
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveWorkbook.Save
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "OOSPivot"
Sheets("Pivot").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets("d").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets("OOS").Select
Range("B8").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 274
ActiveWindow.ScrollRow = 410
ActiveWindow.ScrollRow = 546
ActiveWindow.ScrollRow = 683
ActiveWindow.ScrollRow = 819
ActiveWindow.ScrollRow = 683
ActiveWindow.ScrollRow = 546
ActiveWindow.ScrollRow = 410
ActiveWindow.ScrollRow = 274
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll ToRight:=3
ActiveWindow.SmallScroll Down:=105
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B7:p128").Sort Key1:=Range("P8"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 274
ActiveWindow.ScrollRow = 410
ActiveWindow.ScrollRow = 683
ActiveWindow.ScrollRow = 956
ActiveWindow.ScrollRow = 1228
ActiveWindow.ScrollRow = 1365
ActiveWindow.ScrollRow = 1501
ActiveWindow.ScrollRow = 1637
ActiveWindow.ScrollRow = 1910
ActiveWindow.ScrollRow = 2046
ActiveWindow.ScrollRow = 2183
ActiveWindow.ScrollRow = 2046
ActiveWindow.ScrollRow = 1910
ActiveWindow.ScrollRow = 1774
ActiveWindow.ScrollRow = 1637
ActiveWindow.ScrollRow = 1501
ActiveWindow.ScrollRow = 1365
ActiveWindow.ScrollRow = 1228
ActiveWindow.ScrollRow = 1092
ActiveWindow.ScrollRow = 819
ActiveWindow.ScrollRow = 683
ActiveWindow.ScrollRow = 410
ActiveWindow.ScrollRow = 274
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 1
Sheets("OOSPivot").Select
Range("D1:J1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("D1:J1").Select
ActiveCell.FormulaR1C1 = "Out of Standards Pivot Table"
Range("D1:J1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("G2").Select
Sheets("Pivot").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets("d").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.SmallScroll Down:=405
ActiveWindow.LargeScroll Down:=-1
ActiveWindow.ScrollRow = 288
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 288
ActiveWindow.ScrollRow = 431
ActiveWindow.ScrollRow = 288
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 1
Sheets("d").Select
Range("H5").Select
ActiveSheet.Shapes("Button 1").Select
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Update Data"
With Selection.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.ShapeRange.IncrementLeft 2240.25
Selection.ShapeRange.IncrementTop -18#
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Update Data"
With Selection.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveSheet.Shapes("Button 1").Select
ActiveWindow.LargeScroll ToRight:=-4
ActiveWindow.SmallScroll Down:=-9
Selection.Characters.Text = "Do not Run"
With Selection.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Sheets("Pivot").Select
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("d").Select
Sheets("d").Move Before:=Sheets(6)
Sheets("d").Select
Sheets("d").Name = "Inventory"
Cells.Select
Selection.Copy
Sheets("Pivot").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Buttons.Add(2433, 8.25, 105.75, 22.5).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Rows("1:6").Select
Range("A6").Activate
Selection.Delete Shift:=xlUp
Range("B1").Select

ActiveWorkbook.Worksheets("OOSPivot").PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:="", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="UW", _
ColumnFields:="Workbasket"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("OOS Days")
.Orientation = xlDataField
.Caption = "Count of OOS Days"
.Function = xlCount
End With
ActiveWorkbook.ShowPivotTableFieldList = True
Sheets("Sheet3").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
Cells.Select
Selection.Delete Shift:=xlUp
Range("A3").Select
Sheets("Inventory").Select
ActiveWindow.SmallScroll Down:=-9
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 1
Selection.Copy
Sheets("Pivot").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Pivot!R1C2:R3298C16").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="UW", _
ColumnFields:="Workbasket"
With ActiveSheet.PivotTables("PivotTable3").PivotFields("OOS Days")
.Orientation = xlDataField
.Caption = "Count of OOS Days"
.Function = xlCount
End With
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
Columns("B:B").ColumnWidth = 6.86
Columns("B:B").ColumnWidth = 6.14
Columns("C:C").ColumnWidth = 11.57
Columns("C:C").ColumnWidth = 12.14
Columns("D:D").ColumnWidth = 12.43
Columns("E:E").ColumnWidth = 12.71
Columns("F:F").ColumnWidth = 13
Columns("G:G").ColumnWidth = 8.14
Range("G4").Select

ActiveSheet.PivotTables("PivotTable3").PivotFields("Workbasket").PivotItems( _
"Loss Prevention/Benefits").Caption = "L/P"
Range("G5").Select
Columns("G:G").ColumnWidth = 4.14
Columns("H:H").ColumnWidth = 10.14
Columns("I:I").ColumnWidth = 5.14
Columns("J:J").ColumnWidth = 7.29
Columns("K:K").ColumnWidth = 8.14
ActiveWindow.SmallScroll ToRight:=1
Columns("L:L").ColumnWidth = 4.57
ActiveWindow.SmallScroll ToRight:=-1
Columns("A:A").ColumnWidth = 9.43
Range("D1:H1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
ActiveCell.FormulaR1C1 = "Inventory of Workpackets"
Range("D1:H1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Application.CommandBars("PivotTable").Visible = False
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "InventoryPivot"
Range("E29").Select
Sheets("Pivot").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\mxambriz\My Documents\Book1.xls",
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Sheets("Sheet3").Select
Cells.Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
Sheets("ARO").Select
ActiveWorkbook.Save
Application.run "Book1.xls!AROINVENTORY"
Sheets("OOS").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 274
ActiveWindow.ScrollRow = 410
ActiveWindow.ScrollRow = 546
ActiveWindow.ScrollRow = 683
ActiveWindow.ScrollRow = 819
ActiveWindow.ScrollRow = 1092
ActiveWindow.ScrollRow = 1365
ActiveWindow.ScrollRow = 1637
ActiveWindow.ScrollRow = 1774
ActiveWindow.ScrollRow = 1910
ActiveWindow.ScrollRow = 2046
ActiveWindow.ScrollRow = 2319
ActiveWindow.ScrollRow = 2456
ActiveWindow.ScrollRow = 2592
ActiveWindow.ScrollRow = 2728
ActiveWindow.ScrollRow = 2865
ActiveWindow.ScrollRow = 2728
ActiveWindow.ScrollRow = 2592
ActiveWindow.ScrollRow = 2456
ActiveWindow.ScrollRow = 2319
ActiveWindow.ScrollRow = 2046
ActiveWindow.ScrollRow = 1774
ActiveWindow.ScrollRow = 1501
ActiveWindow.ScrollRow = 1365
ActiveWindow.ScrollRow = 1228
ActiveWindow.ScrollRow = 1092
ActiveWindow.ScrollRow = 956
ActiveWindow.ScrollRow = 819
ActiveWindow.ScrollRow = 683
ActiveWindow.ScrollRow = 546
ActiveWindow.ScrollRow = 410
ActiveWindow.ScrollRow = 274
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll Down:=114
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.LargeScroll Down:=-2
ActiveWindow.LargeScroll ToRight:=-1
Sheets("OOSPivot").Select
Range("B9").Select
Sheets("Pivot").Select
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Macro delete certail column's 1
Macro code 0
Combine 3 Macros 4
macro to copy rows that contain a cell with a certain number 3
Macro Error in Command Button 4
Macro that deletes and pastes filterd rows and then some 2
Data samples 3
Deactivate macros but reactivate again once another macro is run 2

Top