VBA Code to derive filename from bits of information from multiple cells

[sandragm]

Hi everyone!


I’m trying to automate certain aspects of my invoice making process but I haven’t got much experience with VBA, so I was hoping someone could help me out. I’m trying to figure out how to set up a VBA macro to save a worksheet as a pdf with a filename derived from bits and pieces of information already in my workbook.

I did figure out how to achieve it via a formulas on the worksheet, but I would still like to know if and how it could be set it up in VBA because that just seems cleaner to me. I’ve already managed to set up macro buttons for other functions and ideally I’d like to have a button for each of the things I need to do frequently. For reference I’m using Microsoft Excel for Mac via a Microsoft 365 subscription.

So here goes:

This is the formula I set up (if you have any pointers on how to make that formula more efficient, I welcome the feedback as well):

=MID($F$6,5,LEN($F$6)) & " " & (LEFT(F6,3)) & " " & "INV"& " " & (VLOOKUP(Project_Name,'PROJECTS LIST'!A4:B8,2,FALSE)) & " " & IF(ISBLANK($F$9),"",TEXT($F$9,"dd")&" - " & UPPER(TEXT((IF(DAY($F$9)=16,EOMONTH($F$9,0),IF(DAY($F$9)=1,$F$9+14,""))),"dd mmm")))

And this is the returned value, which is the filename format I want to achieve: 01 ABC INV PROJECT NICKNAME 01 - 15 JAN

Context:

What follows are the values of the cells I referenced in the formula.

1. F6 contains my invoice number with a three-letter prefix: ABC_01
2. F9 is a cell where I input the first day of that invoice’s billing period, which could be either the 1st or the 16th of any given month: 01/01/2022
3. Project_Name is a named range on my Invoice worksheet. I have this cell set up as a dropdown list that pulls from a list that’s on a separate worksheet (called PROJECTS LIST) of all the projects I’m currently assigned to.
4. PROJECTS LIST: on this worksheet, the column A contains all the names of the projects I’m assigned to. The adjacent column, column B has the PROJECT NICKNAME of each project. Essentially, I made this in order to have an abbreviated version of each project’s name to pull from for file naming purposes. I figured it’d be useful because the full names of the projects can get quite wordy. These two columns form the A4:B8 range seen in the formula.

So as you can see, I’m pulling little bits and pieces of information that are already in my workbook. I suppose I could set up a macro to bring up the SaveAs dialog box and just manually input the filename myself whenever I need to save a new invoice… but that can get tedious as I sometimes have to prepare 5-7 different invoices per billing period. The whole process is time consuming enough, so I quite like the idea of just pushing a button to save the files as I go.

Any idea how to do this?