I work with multiple excel spreadsheets that require monthly updates for month end financial closing. I need to change references to outside workbooks so that it pulls in data from the current months file.
For example changing
"C:\Desktop\Folder\June"
to
"C:\Desktop\Folder\July"
I want to be able to enter the two months into specific cells in the worksheet and then run a VBA script to auto update the references throughout the workbook. I can record a "macro" for the Find & Replace function, but I can't figure out how to point the "Find" and "Replace" variables to the specific cells where I store the month values.
Here is the current code I have for conducting find and replace:
ActiveCell.Replace What:="Jun", Replacement:="Jul", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Replace the literal text strings of "Jun" and "Jul" with a cell reference. For this, you can use Range("A1"). Of course, you would put in the actual range.
ActiveCell.Replace What:=Range("A1"), Replacement:=Range("B1"),LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False`
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments