Macro that will Save as PDF and then readjust filters and save again and again and again..

Hello All,

Not sure if this is possible but hopefully one of you geniuses can figure this out...

EN BG Report (Working Draft).xlsx

I would like a macro that will Save as PDF and then readjust filters and save again and again and again..

The current filter is "-" and "Anastos". I would like the file to save as Anastos.pdf
The next file the filter would need to adjust to "-" and "Anderson" (The next name on the filter) and save as Anderson.pdf

I would like to print off all names as PDFs in my filter while keeping the "-" as it represents the headers and spacing.

I much appreciate everything you guys do here!!!

Thanks!

Jason

Hi Jason,

I am at work so I won't open your spreadsheet - already been busted for infecting work with a virus.

However what I do on one of my spreadsheets is have a list;

List 1
ABC
CDF
EYX

And my spreadsheets are saved as;
Data_ABC.xlsx
Data_CDF.xlsx

I just created a loop that will go through each cell in List 1.
I just create a generic variable such as XXX = Current cell value in the loop.


Then I instruct VBA to open that data file via;

Please Login or Register  to view this content.

And then save as a PDF via;

Please Login or Register  to view this content.

Might give you a few ideas.

In your attachment the only thing I see is Anastos a bunch of times and the minus sign (-) also a bunch of times.
Where do you get the Anderson from?
Rows are hidden above Row24.
IMHO, an attachment should be a guide, not a puzzle.

Maybe this is a start.
It'll put the unique values from Column "N", starting at N24, into an array and print the sheet named as each value in that array once.

Please Login or Register  to view this content.

Originally Posted by tejay

Hi Jason,

I am at work so I won't open your spreadsheet - already been busted for infecting work with a virus.

However what I do on one of my spreadsheets is have a list;

List 1
ABC
CDF
EYX

And my spreadsheets are saved as;
Data_ABC.xlsx
Data_CDF.xlsx

I just created a loop that will go through each cell in List 1.
I just create a generic variable such as XXX = Current cell value in the loop.


Then I instruct VBA to open that data file via;

Please Login or Register  to view this content.

And then save as a PDF via;

Please Login or Register  to view this content.

Might give you a few ideas.

Hi Tejay,

Let me chew over this idea. Problem is that the names will be changing almost daily. I would like to do a save of the pdf's every other two weeks or (time permitting) but people will be added and removed frequently.

Originally Posted by jolivanes

In your attachment the only thing I see is Anastos a bunch of times and the minus sign (-) also a bunch of times.
Where do you get the Anderson from?
Rows are hidden above Row24.
IMHO, an attachment should be a guide, not a puzzle.

N3 is the filter. There are 368 names (on this version).

I'm not sure what you mean by guide? The problem is the names are constantly being added and removed...

Originally Posted by jolivanes

Maybe this is a start.
It'll put the unique values from Column "N", starting at N24, into an array and print the sheet named as each value in that array once.

Please Login or Register  to view this content.

Thanks, the PDF'ing and naming works. Just not the filtering. Maybe easier if the filter was removed and the macro will sort filer the values itself?

Yes, like I mentioned before, the code will put each name in the list once and from that array makes the PDF's
No filtering needed.
Try it without filters and see what happens.
Let us know

Good luck

Thanks jolivanes,

But i need to filter otherwise all the files are the same. Each file i would like to see only information pertaining to that person's name (and the "-").

I could make a list of all the names in another file.. if that helps to delete every line except the one name and "-" and save as pdf. Then cycle back to the original file and do the same for the next name?

Hi Jason.
OK, I think I see now.
You need to filter and then print to PDF. Do this for all individuals. Is that right?
It doesn't really matter where the list with names is. If you have a list without duplicates you can just work off that.
Let us know what you need.

Excel only prints the visible cells so all we would need is a relation between filtering and individual names, right?
Do you filter on all the non duplicate names?
Can you explain what the criteria is for filtering and the printing. In your first post you mentioned "-" and "Anastos" but I doubt that you want to save a file with a name like "-"

Originally Posted by jolivanes

Excel only prints the visible cells so all we would need is a relation between filtering and individual names, right?

The individual name is every file that I would like to create a report for. The reason why there are duplicates for ANASTO (now that it's been filtered) is that I want all the lines showing that has to do with ANESTO (Line 24, 211, 354, 371... etc). If i filter for ALLEN it would show lines applicable to ALLEN (Line 4, 23, 379... etc).

Do you filter on all the non duplicate names?

There isn't any name without duplicates.

Can you explain what the criteria is for filtering and the printing. In your first post you mentioned "-" and "Anastos" but I doubt that you want to save a file with a name like "-"

The "-" represents headers and spacing that are common lines (lines that must be shown on all pdfs) I need to keep this unfiltered.

The name, such as "Anestos" or "Allen" represents the line of information that I want to include in my body of information. Since each name represents the information applicable for he person i'm running the report for.

What I would manually do to print a pdf report for someone:
To run a report for Anesto i would select "-" and "Anesto" in the filter (located at N3) and save as PDF, name the file "Anesto.pdf".
To run a report for Allen i would select "-" and "Allen" in the filter (located at N3) and save as PDF, name the file "Allen.pdf".
To run a report for Anderson i would select "-" and "Anderson" in the filter (located at N3) and save as PDF, name the file "Anderson.pdf".

Hope that answers the questions.

There probably an easier way to do this but this is what i've been doing manually for all 363 people and really hope there is a macro that can do this as it's fairly repetitive.

In your attachment you have names in N4:N370, N374:N2810, N2813:N5927 and N5930:N7186
Do you filter and print on all of these?

Originally Posted by jolivanes

In your attachment you have names in N4:N370, N374:N2810, N2813:N5927 and N5930:N7186
Do you filter and print on all of these?

Hi Jolivanes,

I'm not sure what you mean. I set the Print Area to print from Column B to Column L. Column N is only a filter. I'm not sure how else to better explain but N is only a reference for what I want to save / print as a PDF.

Attached Copies of a couple examples of the PDF (with print area set to only B and L) for Allen and Anestos.

ALLEN.pdf
ANESTOS.pdf

Note the Folder (C:\Jason) where the files are saved in.
Change as required.

Do you want to print all the white space (empty Columns)?
If not, let us know the range/area you do want to print

In the code the Sheet Name is "Sheet1"
Change to the proper Sheet Name

Check all references in case I forgot one (or more)

Run the code and go for a coffee while it prints 281 files.
It also saves the "-" as a file. You can't delete the "-" because you need a continues column down (no empties).

Please Login or Register  to view this content.

Let us know if it worked.

Good Luck

Originally Posted by jolivanes

Note the Folder (C:\Jason) where the files are saved in.
Change as required.

Do you want to print all the white space (empty Columns)?
If not, let us know the range/area you do want to print

In the code the Sheet Name is "Sheet1"
Change to the proper Sheet Name

Check all references in case I forgot one (or more)

Run the code and go for a coffee while it prints 281 files.
It also saves the "-" as a file. You can't delete the "-" because you need a continues column down (no empties).

Please Login or Register  to view this content.

Let us know if it worked.

Good Luck

Hi Jolivanes,

Thank you again for all the help here.

But... it works with printing all the files as pdf and filtering but removes the "-" which i want to keep selected.

See the two attached files:

Attachment 429603 Allen - this is how i would like the file to look after it prints.
ALLEN (Macro Produced).pdf Allen - this is the file the macro produced. It's almost correct but filtered out the "-" which i want to keep on every print.

Thanks again,

Would it help if it was filter by name... except for Lines 371:373, 2811:2812 and 5928:5929?

Can you change this line (I evidently forgot some periods in that line also)

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

I have not tested it Jason, mainly because I don't feel like saving 281 files on my old machine.

But let us know if that is what you want.

Good luck

Originally Posted by jolivanes

can you change this line (i evidently forgot some periods in that line also)

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

i have not tested it jason, mainly because i don't feel like saving 281 files on my old machine.

But let us know if that is what you want.

Good luck

perfect!!! Works great!!! Love it!!! Thank you thank you thank you!!!

Glad it works for you

Good luck

Flames played well but still ended up short.

From your PM, saying that it worked fine last week but now it does not work any more.
Something must have changed.
Did you change the path?

Can you show us the whole code or better yet, attach a slimmed down version of your workbook that includes the code of course.

BTW, just use the open forum. The more people see a problem the better the chances are of a quick result to solve it.

Hi Jolivanes,

No, i figured out that it is another sheet that I added into the workbook is causing the error. When i delete the "New" sheet the macro seems to work fine. The name of the worksheet is completely different so not sure why this is happening.

I just tried to slimmed down the copy but now the sheet is 5MB and forum will not let me upload more than 1MB. I deleted just about everything including dead space and condition formats.... but can't seem to get it lower then 1.26MB

Can i send this to you via e-mail?

Thanks,

Originally Posted by JasonNeedsHelp

Hi Jolivanes,

No, i figured out that it is another sheet that I added into the workbook is causing the error. When i delete the "New" sheet the macro seems to work fine. The name of the worksheet is completely different so not sure why this is happening.

I just tried to slimmed down the copy but now the sheet is 5MB and forum will not let me upload more than 1MB. I deleted just about everything including dead space and condition formats.... but can't seem to get it lower then 1.26MB

Can i send this to you via e-mail?

Thanks,

Oh my, just moved the new sheet to the left of the the sheet that is macro-ed and it works! Why is that? But now it works...LOL