VBA Change Pivot filter and export pdf file

Question

I am trying to automize a lot of my current excel work. Atm i have about 30 filters in my pivottable that i need to change one at the time and then export as a pdf file. I was thinking it might be possible to create some VBA code that automatically changes the filter and exports a pdf (to a local folder) and then automatically moves on and changing next filter and create a pdf from that, and so on, 30 times.

I have tried to find some guide for it, but it has not been possible for me. Do you have any idea on how i can get started?

Have you tried recording a macro covering a couple of iterations? — Mark Fitzgerald, Jul 06 '16 at 13:26

score 1 · Answer 1 · edited May 23 '17 at 11:44

1

Perhaps look into the two topics separately then combine.

1: Changing filters you could do via recording a macro then editing the results if you can't make sense of something like this.

2: Saving as pdf is a little trickier, as you could use many things to do it, depends if you have adobe on your machine etc or if you're using things like pdfcreator etc, this is an example where someone is doing something similar.

Give it a go and post your code back and people will be much more willing to try and help you.

edited May 23 '17 at 11:44

Community

1
1

answered Jul 06 '16 at 13:22

Tim Edwards

1,031
1
13
34

I'll have to comment on this as I don't have enough reputation to comment on your answer but in terms of what you have there, you could change the string after `Filename:=` to be dynamic, i.e. "C:\Users\username\Folder\Folder\Documents\Folder\Folder\Filename" & i & ".pdf" and increase i for each file. – Tim Edwards Jul 06 '16 at 15:43

score 0 · Answer 2 · answered Jul 06 '16 at 14:42

Okay so i found that i can Record a macro to:

Change filter
Save as PDF

which works perfectly, however i need the saved filename to be dynamic since i need 30 PDF files, and not 1.

The recorded code looks like this

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\username\Folder\Folder\Documents\Folder\Folder\Filename.pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False

Do you know how i then change the saved file name based on a cells name?

VBA Change Pivot filter and export pdf file

2 Answers2