Forum Discussion
Auto Select data one by one from data filter of a pivot table using VBA MACROS
Hi!!!
I need some help to write a VBA macro code to auto select a data one after another from a data filter field of a pivot table.
Sheet 1 consists of all datas and Sheet 2 consists of Pivot table and Sheet 3 consists of a report format.
Had tried macro, but whenever Sheet 1 data is updated, while running a macro with updated pivot data filter, it picks the previous set of datas.
Could anyone please help.
Thanks and Regards
Sridhar
- JKPieterseSilver ContributorPlease show us your code. DOes it work as expected if you click refresh all prior to running your code?
- SRIDHAR RAMESHCopper Contributor
Dear Jan,
Thanks for your reply.
Pls find the attached working sheet and the vb code given below.
Yes, had refreshed the datas before i run the macro, but when i run the macro
whatever is recorded in the previous file, it applies the same, which is resulting in error.
I got this code by macro recording only by selecting three no of datas, as i do not know how to do coding.
Practically i will have to generate the reports for 200-500 datas every two days.
You could very well imagine as how hard it is to select the data from a pivot
sheet and to give the print out.
Had tried to solve this by myself by going through different you tube videos and articles, but i couldn't succeed.
Looking forward your reply. If you could help me out , it will save me a lot of time.
Thanking you in advance.
Best Regards
Sridhar
Sub GPPRINT() ' ' GPPRINT Macro ' ' Range("B1").Select ActiveSheet.PivotTables("PivotTable6").PivotFields("BL Number").CurrentPage = _ "YNN047" Sheets("GATE PASS").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False Sheets("Data").Select ActiveSheet.PivotTables("PivotTable6").PivotFields("BL Number").CurrentPage = _ "YNNN04895" Sheets("GATE PASS").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False Sheets("Data").Select ActiveSheet.PivotTables("PivotTable6").PivotFields("BL Number").CurrentPage = _ "YNNN08658" Sheets("GATE PASS").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False End Sub- JKPieterseSilver Contributor
Perhaps like so?
Sub GPPRINT() Dim oPi As PivotItem With Worksheets("Data").PivotTables("PivotTable6").PivotFields("BL Number") For Each oPi In .PivotItems .CurrentPage = oPi.Value Sheets("GATE PASS").PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False Next End With End Sub