VBA & Macro Problems

VBA Code to Automatically Copy Previous Week's Data in Excel

Phi Hung Vo Oct 1, 2026 868 views

Question details

The user wants a VBA macro to automatically identify the previous Monday through Friday and copy the corresponding data from source workbooks without having to manually update fixed ranges every week.

How to Use VBA to Automatically Copy the Previous Monday to Friday Data in Excel

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Preparing weekly presentation charts by pulling the previous week's auto-updated data from two master files into destination worksheets.
Observed behavior: The current approach requires manually changing hardcoded data ranges (like A1:T10000). The user needs the macro to calculate the date boundaries dynamically and pull new records automatically.

Before you start

Ensure your source workbooks have a dedicated column containing valid dates for the records, and save your file as a Macro-Enabled Workbook (.xlsm) to allow VBA execution.

Solution 1Recommended

Filter and Copy Using Dynamic Date Calculations in VBA

Calculate the exact dates for the previous Monday and Friday using VBA Date and Weekday functions, filter the data range dynamically, and copy only the visible rows.

By determining the current week's Monday and subtracting seven days, VBA can reliably find the previous Monday. Adding four days to that gives the previous Friday. We can then apply an AutoFilter based on these boundaries to dynamically copy new records without hardcoding cell ranges.

Open the Visual Basic Editor

Press Alt + F11 in Excel to open the Visual Basic Editor, then right-click on your workbook name in the Project Explorer and select 'Insert' > 'Module'.

Define Date Variables

Declare your variables for StartDate and EndDate as 'Date'. Calculate the previous Monday (StartDate) using the formula: StartDate = Date - Weekday(Date, vbMonday) - 6.

Calculate the Previous Friday

Calculate the EndDate (previous Friday) by adding 4 days to the calculated StartDate: EndDate = StartDate + 4.

Find the Dynamic Last Row

Define a variable for the last row (e.g., LastRow) and calculate it using: LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row. This ensures the macro captures all newly added records.

Apply AutoFilter by Date

Apply the AutoFilter method to your date column (e.g., Field:=1) using Criteria1:=">=" & StartDate and Criteria2:="<=" & EndDate.

Copy and Paste Visible Cells

Use Range.SpecialCells(xlCellTypeVisible).Copy to copy only the filtered data. Specify your destination workbook and worksheet, and paste the data into the first empty row below the existing data.

Filter and Copy Using Dynamic Date Calculations in VBA

Formatting Filter Criteria: If AutoFilter fails to recognize the VBA dates, try converting your StartDate and EndDate variables to Long format during filtering by using CLng(StartDate) and CLng(EndDate).

Easily Manage VBA Macros with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for VBA macros, allowing you to seamlessly run dynamic date copying scripts. It is fully compatible with Microsoft Excel's .xlsm files and offers a robust developer environment for automating your weekly reports.

1. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your existing .xlsm file that contains your weekly source data.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'Visual Basic' to launch the integrated VBA Editor.
3. Insert the Dynamic Script: Right-click 'VBAProject', insert a new Module, and paste your dynamic date calculation code.
4. Execute the Macro: Save your code, return to your spreadsheet, and click 'Macros' > 'Run' to instantly filter and copy the previous week's data.

100% compatible with Microsoft Excel formats (.xls, .xlsx, .xlsm)Built-in robust VBA editor for writing, debugging, and executing macrosLightweight application that opens large data files incredibly fastAdvanced data filtering and charting tools perfect for weekly presentations

microsoft office alternative - wps office

Frequently Asked Questions

How does VBA determine the current date for calculations?

VBA utilizes the built-in Date function, which returns the current system date of your computer. You use this as the starting anchor for calculating historical dates like previous weeks or specific weekdays.

Why is my VBA copying blank rows instead of the filtered data?

This commonly happens if the AutoFilter criteria do not match the formatting of your date column. Ensure your source data dates are recognized as actual dates in Excel (not text strings) and format your VBA filter criteria properly, sometimes requiring the CLng() conversion.

Can I copy data from a closed workbook using VBA?

While pulling data from a closed workbook is possible using ADO, the standard and most reliable VBA method involves writing a macro that briefly opens the source workbook in the background (Workbooks.Open), copies the required data, and closes it (ActiveWorkbook.Close).

How do I prevent pasting over existing data in my destination sheet?

You can dynamically find the first empty row in your destination sheet by determining the last used row and adding one to it. Use a formula like: NextFreeRow = Worksheets("Destination").Cells(Rows.Count, 1).End(xlUp).Row + 1.

Excel & Data Problems