G
Guest
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...........
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
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...........
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