Move Date-Named Excel Files with VBA in WPS Office
Question details
The user needs a VBA macro that moves three Excel files from a source folder to destination folders when the file names include a changing date, using a date variable to build the file names and remove the originals after copying.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Daily date-named Excel workbooks need to be moved from a source folder to specific destination folders.
- Observed behavior
- The macro should copy each date-matched workbook to its destination and delete the source file only after a successful copy.
Confirm the exact source and destination folder paths, and check that the date in the file names uses the yyyymmdd format expected by the VBA Format function.
Create a date-aware VBA macro to move three Excel files
Use this solution when the file names contain a changing date and you want to copy each workbook to its destination, then remove the source file.
This macro builds each file name from previousDate = Date - 1 and Format(previousDate, "yyyymmdd"), then copies each .xlsx file to its destination and deletes the source file with Kill only after the copy operation succeeds.
In WPS Spreadsheets, click Tools > Macro > Visual Basic Editor, or press Alt+F11, to open the VBA editor.
In the Visual Basic Editor, click Insert > Module to create a module where the macro code can be pasted.
Paste this pattern into the module and update the folder paths: File1 = "File1_" & Format(previousDate, "yyyymmdd") & ".xlsx" FileCopy SourceFolder & File1, DestinationFolder & File1 Kill SourceFolder & File1 Repeat for File2 and File3.
Press F5 while the cursor is inside the macro, then open the destination folders to confirm the three date-named .xlsx files were copied; also verify the source folder no longer contains the originals.
Automate Date-Named Excel File Moves with WPS Office VBA
WPS Spreadsheets includes the Visual Basic Editor and supports VBA FileCopy and Kill methods, so you can build the same date-aware macro to move your .xlsx files directly in WPS Office.
- 1. Download WPS Office: Go to the WPS Office download page, install WPS Office, and open WPS Spreadsheets.
- 2. Open the VBA editor: In WPS Spreadsheets, click Tools > Macro > Visual Basic Editor or press Alt+F11.
- 3. Insert a module and paste the macro: Click Insert > Module, paste the date-aware FileCopy/Kill macro, and update the SourceFolder, DestinationFolder, and file name pattern.
- 4. Run and verify the move: Press F5 to run the macro, then confirm the date-named .xlsx files are in the destination folders and the source originals are removed.
Frequently Asked Questions
How do I build a file name based on the previous day's date in VBA?
Use previousDate = Date - 1, then build the name with Format(previousDate, "yyyymmdd"), for example: File1 = "File1_" & Format(previousDate, "yyyymmdd") & ".xlsx".
Does FileCopy move the file by itself?
No. FileCopy copies the file to the destination. You then use Kill to delete the original source file after confirming the copy succeeded.
Where do I open the Visual Basic Editor in WPS Spreadsheets?
Click Tools > Macro > Visual Basic Editor, or press Alt+F11, then insert a module from the Insert menu.
What should I check before running the date-based moving macro?
Confirm that the folder paths exist, the source files are closed, and the actual file names match the yyyymmdd date pattern used in the macro.
Can the macro be changed to use today's date instead of the previous day?
Yes. Use currentDate = Format(Date, "yyyymmdd") instead of previousDate = Date - 1 and keep the same FileCopy and Kill structure.
