How to Use the Previous Monday's Date in Excel VBA Sheet Formulas
Question details
Dynamically reference a worksheet named after the previous Monday using VBA to pull data into a newly created weekly sheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating a weekly reporting procedure where a new worksheet is copied from a template and needs formulas that carry forward data from the previous Monday's sheet.
- Observed behavior
- The user is currently using hard-coded worksheet names and needs a VBA procedure to calculate, format, and insert the exact string of the previous Monday's date into the sheet formulas.
Verify the exact date format used for your existing worksheet names (for instance, 'MM-DD-YY' versus 'M-DD-YY'), as a mismatch will result in broken formula references.
Calculate and Concatenate the Previous Monday Date in VBA
Use VBA's DateAdd and Weekday functions to find the appropriate date, format it as a string matching your worksheet names, and build it into your Range.FormulaR1C1 assignment.
To make your weekly sheet creation fully dynamic, you can mathematically determine the date of the previous Monday relative to the current date. Once calculated, you convert that date into a formatted string that matches your tab naming convention and inject it into your formula strings.
Open the VBA Editor (Alt + F11) and declare variables for your dates and strings at the start of your subroutine. For example: `Dim dNext_Monday As Date` and `Dim sLastMonday As String`.
Calculate the upcoming Monday using `dNext_Monday = DateAdd("d", -Weekday(Now) + 9, Now)`. You can then subtract 7 days from this variable to accurately pinpoint the previous Monday.
Use the VBA Format function to match your worksheet naming convention perfectly. For example: `sLastMonday = Format(dNext_Monday - 7, "MM-DD-YY")`.
Assign the formula using `FormulaR1C1`. Be sure to wrap the dynamic sheet name in single quotes to prevent errors. Example: `Sheets(2).Range("B3").FormulaR1C1 = "='" & sLastMonday & "'!RC[12]+3"`.
Automate Worksheets Seamlessly with WPS Office
WPS Spreadsheet offers robust, built-in support for VBA and macros, allowing you to run your dynamic date calculation scripts and automate your weekly reporting tasks exactly as you would in Microsoft Excel.
- 1. Open your macro workbook: Launch WPS Spreadsheet and open your existing Macro-Enabled Workbook (.xlsm).
- 2. Access the VBA Editor: Navigate to the Developer tab or simply press Alt + F11 to open the built-in VBA Editor.
- 3. Insert your VBA code: Paste your dynamic previous-Monday calculation and sheet generation macro into the appropriate module.
- 4. Run your automation: Execute the macro to automatically copy your template, apply the dynamic date name, and insert the formulas referencing the prior week.
Frequently Asked Questions
Why is my VBA formula returning a #REF error when referencing the previous Monday's sheet?
This usually happens because the date string generated by your VBA Format function (e.g., "01-02-23") does not perfectly match the actual name of the previous sheet (e.g., "1-02-23"). Ensure your `Format(date, "format")` matches the naming convention exactly.
Can I adapt this VBA code to calculate the previous Friday instead?
Yes. You can adjust the math inside the `DateAdd` calculation or subtract a different number of days from your base calculation to target any specific day of the previous week you need.
How do I correctly handle single quotes for sheet names within VBA formulas?
When building formula strings in VBA, you should always wrap dynamic sheet name variables in single quotes (e.g., `"='" & sheetNameString & "'!A1"`). This ensures your formulas evaluate correctly even if the sheet name contains spaces, dashes, or special characters.
