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 but I'm not confident creating more 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.
I should say that the workbook is password protected and the password is: Evaluation
Thank you
Its not perfect or the most efficent as i have tried to keep your formating and i dont want to break anything else, but i have made a loop on the unsuccessful bidders.
I also dont know if there are any other parts of the Macro that limit the number of letters, but i have tested with 7
The pdf's are currently being saved to SavePath = "C:MOTH" so you will need to uncomment the savepath line in the AllPDFS Module
Just click the Create PDFs button
Purfleet
Hi Purfleet
This is amazing. Thank you so much.
I've adjusted the file path and it is working brilliantly. One fly in the ointment though. The macro seems to create one less unsuccessful letter than there are unsuccessful bidders. So if there were 7 bidders it generates 1 award report, 1 successful letter and 5 unsuccessful letters when there should be 6.
Sorry to be a pain but I can't see what I should adjust in the macro to get the final unsuccessful letter to save as a PDF.
hmmm, it created 6 for me - do you know what one is missing?
That's really strange, I've just tried it again and it's worked perfectly! Thank you so much for your help 🙂
I've just tried adding in the address information for each of the bidders and now it wont create the pdfs for the unsuccessful bidders.
In the Create All PDFs macro it's debugging at
Worksheets("Unsuccessful Letter Temp").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
SavePath & "UnsuccessfulLetter " & Bidder & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
It creates the Award Report and the Successful Bidder PDFs perfectly
