Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP** Pivot table auto updating
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:=xlDatab ase, 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE HELP** Pivot table auto updating
You need this line in your macro.
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh You can record it by clicking on the Pivot tabel and refresh ! it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating auto Updating Pivot Table linked to external data. | Excel Discussion (Misc queries) | |||
updating pivot table | Excel Discussion (Misc queries) | |||
Auto updating a table | Excel Discussion (Misc queries) | |||
Auto updating pivot tables using external XML data | New Users to Excel | |||
Pivot Table not Updating | Excel Worksheet Functions |