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

sandragm

New Member
Joined
Feb 18, 2022
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
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):

Excel Formula:
=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?
 

Attachments

  • Screen Shot 1.jpg
    Screen Shot 1.jpg
    178.5 KB · Views: 16
  • Screen Shot 2.jpg
    Screen Shot 2.jpg
    142.1 KB · Views: 16

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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