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

Occasional Contributor

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

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

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

 

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




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

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

 

Dear Jan,

 

Noted.

 

Had ran both of the macros, but i am getting error.

 

Pls find the screenshots in the word doc attached for your reference.

 

Pls help to rectify it.

 

Thanks and Regards

Sridhar

For the first error: make sure the code uses the correct worksheet names.
The second I don't know, it worked for me.

Dear Jan,

 

Thanks for your help.

 

Will try this one.

 

Best Regards

Sridhar