Activate file with file name depending on cell value changed

[Linda Lo]

Hi,

I am a fresh VBA learner. I have a problem in running the following macro. Could someone help me to point out?
Before I run it on 1st day and save as "20230515 HKD (with FMS new version upload).xlsm". I will run it again in next day and I need to add cell value before the file name in order to activate it when running it because of file name changed.

1st date run:

Windows("HKD (with FMS new version upload).xlsm").Activate

Dim FPath As String

Dim FDate As String

Dim FName As String

Cells(1, 4).Value = "=TEXT(RC[-1],""yyyymmdd"")"

FPath = "H:\TEMP\BEN\!PAU\PAU Sheet"

FDate = Cells(1, 4).Value

FName = " HKD (with FMS new version upload)"

ActiveWorkbook.SaveAs Filename:=FPath & "\" & FDate & FName

I found a error when 2nd date run, "20230515" will be the format value of Range("C1") in "20230515 HKD (with FMS new version upload).xlsm" file.

2nd date run:

Dim FileDate As String

FileDate = Range("C1").Value

Range("D1") = "=TEXT(C1,""YYYYMMDD"")"

Windows("FileDate" & " " & "HKD (with FMS new version upload)" & ".xlsm").Activate

Thanks a lot.

Linda

 

[OaklandJim]

Is there any chance that you can provide a link to the workbook? Fake data is fine. I'll try to help you get the code setup. Use the link icon at the top of the message box. You can post part of a worksheet with Mr. Excel's addin XL2BB. See HERE.