VBA to change a value and save a result sheet as PDF with value as name

[dip11]

Hi,

Could you please assist me with the following:
In a workbook, I want the macro to change the value of a cell according to a list on a different sheet, while that value is valid (has an associated entry greater than 0)
Once the value has been changed, let it finish calculating and then save a third sheet as a PDF, naming it according to the value + the previous month's date.

Example workbook with comments and details attached

Thanks in advance

P.S. If it can also email the resulting pdf to the email entry that'd be great, but that's just a bonus, not a necessity

[romperstomper]

Could you just state in words what it is you want the code to do since there don't appear to be any calculations going on.

[dip11]

Hi romperstomper,

Thanks for your reply, the code should do:
1. Change field Output!A2 to the PersonCode associated to first value on CaseCounter A2:A60 which has a value greater than 0 in R2:R60 (find PersonCode by going to Sheet2 and looking up the PersonName or PersonNumber)
2. It should then wait for calculations to complete
3. Then it should save the Report sheet as a PDF calling it what it put in Output!A2 + last month's name
4. Then it should email that resulting PDF to the email associated with Output!A2 in Sheet2
5. Then it should change the field Output!A2 to the next value as per step 1.

There are no calculations on the example sheet because it is a dummy workbook. The actual workbook is some 8mb in size and takes ~50 seconds to calculate on 8 processors each time the value in Output!A2 is changed.

The email step is optional, I think its probably best to get it to work first rather than emailing people possibly truncated incomplete files

I hope that helps

I've updated the attachment slightly

[dip11]

Still need help with this if anyone is able.

[romperstomper]

Basic code would be something like this:

Please Login or Register  to view this content.

[dip11]

Thanks romperstomper - How would I need to adjust this to lookup the PersonCode from Sheet2 based on the PersonName or PersonNumber on CaseCounter as the value in Output!A2 is PersonCode and not PersonName or PersonNumber.

[romperstomper]

That's not what your workbook shows! Give me a second...

[romperstomper]

Please Login or Register  to view this content.

[dip11]

Fantastic, thanks so much!

[edit]
Watch your rep - I'll add it to you as soon as the system lets me again (asking me to spread around first). Won't forget!

[romperstomper]

No worries. You may find this page useful for your emailing: http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[dip11]

OK, would i need to download that sample template for the sub() and then insert the code like this?

Please Login or Register  to view this content.

In terms of who to send it to, do I replace the email address with
varMatch = Application.Match(.Cells(lngRow, "A").Value, Sheets("Sheet2").Range("D:D"), 0)

or something else?

Since I already created all the PDFs now before having the email code, can I create it as a shortened macro and run just the email portion somehow (i.e. checking for the PersonCode before the first space and using that to match on Sheet2?)