Apr 10 2018
09:41 AM
- last edited on
Jul 25 2018
09:55 AM
by
TechCommunityAP
Apr 10 2018
09:41 AM
- last edited on
Jul 25 2018
09:55 AM
by
TechCommunityAP
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
Apr 10 2018 11:56 PM
Apr 11 2018 07:45 AM
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
Apr 11 2018 08:40 AM
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
Apr 11 2018 09:21 AM
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
Apr 12 2018 12:41 AM
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
Apr 12 2018 04:01 AM
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
Apr 12 2018 05:16 AM
Apr 12 2018 05:43 AM
Dear Jan,
Thanks for your help.
Will try this one.
Best Regards
Sridhar