Auto Select data one by one from data filter of a pivot table using VBA MACROS

Question

Hi!!!&nbsp;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.&nbsp;Sheet 1 consists of all datas and Sheet 2 consists of Pivot table and Sheet 3 consists of a report format.&nbsp;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.&nbsp;Could anyone please help.&nbsp;Thanks and RegardsSridhar

jkpieterse · Answer

Please show us your code. DOes it work as expected if you click refresh all prior to running your code?

sridhar ramesh · Answer

Dear Jan,&nbsp;Thanks for your reply.&nbsp;Pls find the attached working sheet and the vb code given below.&nbsp;Yes, had refreshed the datas before i run the macro, but when i run the macrowhatever is recorded in the previous file, it applies the same, which is resulting in error.&nbsp;I got this code by macro recording only by selecting three no of datas,&nbsp;as i do not know how to do coding.&nbsp;Practically i will have to generate&nbsp;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 pivotsheet and to give the print out.&nbsp;Had tried to solve this by myself by going through different you tube videos and&nbsp; articles, but i couldn't&nbsp;succeed.&nbsp;Looking forward your reply. If you could help me out , it will save me a lot of time.&nbsp;Thanking you in advance.&nbsp;Best RegardsSridhar&nbsp;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&nbsp;

jkpieterse · Answer

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

sridhar ramesh · Answer

Dear Jan,&nbsp;Thanks for the quick reply.&nbsp;I have one more doubt, by using this code, i can select n no of datas from a pivot table data filter and print the reports with a single click on run macro right?&nbsp;Will test the macro by tomorrow and will give you the feedback.&nbsp;Best RegardsSridhar

jkpieterse · Answer

The macro is limited in a way: It filters and prints out ALL items in the page filter, no exceptions.If you -for instance- have a range of cells with BL numbers you want printed, you need a macro like this one:
Sub GPPRINT_2()
    Dim oCell As Range
    With Worksheets("Data").PivotTables("PivotTable6").PivotFields("BL Number")
        For Each oCell In Worksheets("Sheet1").Range("A1:A10")
            .CurrentPage = oCell.Value
            Sheets("GATE PASS").PrintOut Copies:=1, Collate:=True, _
                                         IgnorePrintAreas:=False
        Next
    End With
End Sub
This prints the sheet for the numbers in cells A1:A10 on Worksheet Sheet1
&nbsp;

Forum Discussion

Auto Select data one by one from data filter of a pivot table using VBA MACROS

Share

Resources