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

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