Forum Discussion

SRIDHAR RAMESH's avatar
SRIDHAR RAMESH
Copper Contributor
Apr 10, 2018

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...
Macros & VBA
Show and Tell
JKPieterse's avatar
JKPieterse
Silver Contributor
Apr 11, 2018
Please show us your code. DOes it work as expected if you click refresh all prior to running your code?
  • SRIDHAR RAMESH's avatar
    SRIDHAR RAMESH
    Copper Contributor
    Apr 11, 2018

    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

     

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Apr 11, 2018

      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's avatar
        SRIDHAR RAMESH
        Copper Contributor
        Apr 11, 2018

        Dear Jan,

         

        Thanks for the quick reply.

         

        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?

         

        Will test the macro by tomorrow and will give you the feedback.

         

        Best Regards

        Sridhar

Resources