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 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

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    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

      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

        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