Macro to PDF on a loop

[NannyNetty]

I have been passed a workbook that is used to process bids for supplying transport for schools. It calculates the winning bid, then generates a letter to the successful bidder, and then letters to the unsuccessful bidders, all of which are then saved as a PDF saved to a specific file path. The macros that generate the PDFs aren't the best - the workbook is currently set up to deal with a maximum of 20 bids, and there are 20 macros to then generate the PDFs. I'm sure there should be a simpler way to do this by having one or two macros that run on a loop based on the number of bidders. The team have said that there may be an occasion when there are more than 20 bids so I think this definitely needs to be on some loop. Unfortunately my VBA knowledge is a little limited, I can read and understand macros to an extent but I'm not confident creating complex ones by scratch so I'm hoping that one of you amazing people could help. I've attached the workbook which I've anonymised as much as I can.

Ideally the workbook would have only one letter template for unsuccessful bids rather than 20 or more

I should say that the workbook is password protected and the password is: Evaluation

Thank you

[Keebellah]

Hi, I'll see what I can do for you.
Have downloaded the workbook and will see if I understand what you're looking at and if I have questions, I'll holler

[NannyNetty]

Thank you

Please shout if you need any clarification on anything

[Keebellah]

Okay, yesterday was a little hectic; hope to have something for you today👌😊

[Keebellah]

First thig, and that's my thing, I hate #VALUE and #DIV/0 in cells.
I corrected your formulas to avoid these and als if a value is 0 the cell looks blank (custom cell format)
Yes, I see you have 20 letter sheets and a quick look tells me they're all the same but only the data varies.
I don't understand why you did that, like you said you started working with this file that someone else put together.
The way it looks and the 'overkill' on similar macros is, well very back practice.

I'll have to read it carefully and hope to come up with something more efficient and not limited to 20 like it's now.
Here's your file returned with the modified formulas, take a look and you'll see what I mean with that (about the formulas)

[NannyNetty]

Hi Keebellah

I must stress this was NOT my workbook it was created by someone in our organisation who has subsequently left and her team didn't have a clue how it worked. I am in the process of trying to clean it up. I agree with you I hate error messages!

My hope was to have a single template for the unsuccessful bidders which updated and pdf'd on a loop until a PDF had been created for each of the unsuccessful bidders.
Overkill is definitely the word. The team have told me that they have an additional workbook with 50 letter templates in it!

[Keebellah]

I understood that much from your first post, like I said will see what I can do and let you know, and yes, if I have questions, you'll hear from me

[Keebellah]

I'm running into a minor issue, the ranking is off, I cross-checked with your original file and don't see why, but ... am almost there and you only need two letter sheets for it run any number of bids
One thing, what do you do when you've got two rankings as 1st?

[NannyNetty]

Ah I hadn't considered there being more than 1 bid ranking as 1st. I'll get back to the team and see what they say. It may take them a couple of hours to respond

[Keebellah]

Okay, figured it out, you take care of the split 1st place

Here's the workbook with three results as well.
You will have to keep in mind that I did not account for duplicate file names so un unsuccessful letter will be overwritten, but that's a a question of adding the bidder winner to the name
How about the Award pdf?
The Successful also , add the winner company name.

The company table is protected and the second column has a formula, DO NOT change that unless you had other things in mind
Letme know

[NannyNetty]

I've just heard back from the team. Apparently if there are 2 winning bids, they look at the merits of each bid and once they have determined the winning bid they then add 0.1 to the other so there will always be a single winning bid

[Keebellah]

Okay, that's a good way to do that.

Made a change to add the Tender No to the pdfs.
I do notice that the Rank formula is still off if I have three bidders though

[Keebellah]

Sorry for going on but the names of the pdf files kept nagging me.
I also added a prompt after the PDFs have been created if you wish to clear the entries
The ranking formula is still an issue, but that I'm sure you can solve

[Keebellah]

Okay, mainly cosmetic functionality.
Added a Userform which is invoked when to press Save PDFs that will allow you to change the destination folder (if required) and also a checkbok that when checked will save the PFS files in their own Tender sub-folder

[NannyNetty]

Hi Keebellah
That is looking great. Brilliant idea to have a form to specify the destination for the PDFs.
One problem - when I click the PDF button it falls down straight away on the User Form Activate macro on the line .PrintButton.Enabled = Val(Worksheets(EVSheet).Range("BIDSRECEIVED")) > 0 And Dir(.PDFfolder.Text, vbDirectory) <> ""

Sorry

[Keebellah]

HI, good morning,
What do you mean with

when I click the PDF button it falls down straight away on the User Form Activate macro

?
Do you mean an error?

But is can be that your Office 2020 doesn't support part of the code even though Icannnot imagine that:

Please Login or Register  to view this content.

Try one of these options:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

If the last one works you'll probably run into another error when actually checking before exporting where the same condition is tested.

The second condition just checks if the folder exists.

This is what you should see instead and if there are no bids the Create PDFs button should be disabled

[Keebellah]

I did some quick teste, maybe this works

[NannyNetty]

That's great I get the form and I can direct the pdfs to the correct folder. Unfortunately it is only saving the Award Report and not the successful/unsuccessful pdfs

[Keebellah]

Are you sure, works here, check your output folder (the selected one).
It should do that, the export code is similar
Here's a screenshot of the output folder, I could emulate your K disk because I have a K-dis too
The Tender number I gave it was 55-756 and see the files in it that sub-folder

You will have to run the code again and step through it form the point the loop runs

[NannyNetty]

Attachment 681985

This is what I get. It all looks like it's working and I get no debugging and then get the option to clear the Evaluation sheet once the pdfs have saved

[Keebellah]

I can't see your attached file (is invalid)
But I modified the code for the folders part and it now works
See PDF with the screen shots step-by-step
and the updated version

[Keebellah]

Have you take a look at the V2.2 version?

[Keebellah]

Pity I haven't heard from you.
Hope it works, I dis think of one thing, a date picker for the two date cells in Evaluation, but, since you're busy I'll leave it as it is and hope it all works now.
Happy coding

[NannyNetty]

Hi Sorry

I had an urgent job that came in late yesterday that had to be completed by Mid Day today.

I've just tried the V2.2 and it is still only generating the Award report and neither the successful no unsuccessful bidder letters. On the letter templates it is not filling in all the information. For example it fills in the % but not the bid rate.

[Keebellah]

I don't understand it works here and the code is the same so with your explanation and no sample, it makes no sense
These are the files it produced adn tell me what's missing