VBA to add title from formula to a save as name

NKUNZI

New Member
Joined
Feb 8, 2019
Messages
13
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
My question is two fold in that I would like to save and print out a work sheet to a pdf file.
the difficulty is first to specify a range to work from, and the secondly to use a name in the saved file name.

Background:- I need to automate printing of class reports, where the report pulls in data from various other worksheets that in essence pulls out the learners name and ID No, followed by a subject listing with the marks obtained.

Currently a student id No is entered which pulls in the relevant data using vlookup formula. This worksheet is firstly printed so that a hard copy can go to the parents and then saved to a pdf file using the student name as the file name which is then emailed to the invigilating body.

I need a way to transform a formula like =VLOOKUP($C$2,'Information Sheet-Database'!$B$4:$I$33,2,FALSE) into the name that is pulled through and preferably with a month parameter, but this is optional.
I need to keep the formula in place, so does not make sense, to Copy and save special to convert the formula to a name, and then have to retype the formula so that the next name on the list can be extracted.
So in essence I need to automate the following steps done manually
File; Save As Shawn Bull - Nov (or simply Shawn Bull), choose file destination, Save as type PDF save

Then go back into the report worksheet, automatically select the next student no, and repeat the print and then save process until all the names have been processed.
The options that we would like is along the lines of --- Save and print ALL ----; Save and print from ---Enter start no ---- to ----Enter end no----; cancel request

This would be great if it could be done using VBA code that could be incorporated into the excel ribbon.
Cherry on the top would be to create a folder into which these pdf files would go, and then check if the folder exists so that remaining files may be stored there

Something like add the folder School Reports to the Desktop, then all reports would be saved in the folder school Reports.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,224,465
Messages
6,178,821
Members
452,881
Latest member
motivationgyan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top