Dynamic change in VBA of a cell address that contain variable results used to print or save a file.

[mad4max]

I have a sheet called PARAM in which I use to store some param that I use for different purposes.

For example, in the cell E8 I have the printer name to be used in VBA to print:

WorkCentre 6515 PS on Ne00:

Then, in cell E10 I use to store the directory name where the file should be saved.

I find it using a formula:

=LEFT(@CELL("filename",Offre!A3),FIND("[",@CELL("filename",Offre!A3))-1)

So, if I move the file in another directory, the result is updated automatically.

In cell E11, I create the filename, concatenating several info’s in several cells:

Result for the Excel file:

E2022.10.29--O9999.v01--2022.08.15--SNO - Société Neuchâteloise des Officiers--Bal de la SNO--Souper--Salle des Chevaliers.xlsm



Where the formula behind is:

="E"&YEAR(Offre!C11)&"."&IF(MONTH(Offre!C11)<10,"0"&MONTH(Offre!C11),MONTH(Offre!C11))&"."&IF(DAY(Offre!C11)<10,"0"&DAY(Offre!C11),DAY(Offre!C11))&"--"&Offre!C1&"--"&YEAR(Offre!C4)&"."&IF(MONTH(Offre!C4)<10,"0"&MONTH(Offre!C4),MONTH(Offre!C4))&"."&IF(DAY(Offre!C4)<10,"0"&DAY(Offre!C4),DAY(Offre!C4))&"--"&Offre!C5&"--"&Offre!C7&"--"&Offre!C8&"--"&Offre!C9&".xlsm"

…and the same in cell E12 for the .pdf file!

Now, my macro looks like:

VBA Code:

Sub madmax()
'
‘ Select the cell that where the file should open every time.
'
With Sheets("Offre")
.Activate
.Range("C3").Select
End With

'
‘ Select the sheet where the info like directory and filename is stored.
‘ and use the content of the specific cell (E10 + E11) as variable
‘
With Sheets("Param")
ChDir (.Range("E10").Value)
ActiveWorkbook.SaveAs Filename:=.Range("E10").Value & .Range("E11").Value
End With

End Sub

My question is:

Is it possible to have a dynamic read of the position of the variables in cell?

Actually, the VBA code point and target a specific location (E10 or E11) in the PARAM sheet.

How to change the VBA automatically if for any reason, I should move the E10 or E11 in another location? Like if I add a line or a column before or between the E10 and E11?

If I move the cells or if I insert a line, the content, move and no errors in the formula.

But, my VBA code will still point the old location and doesn’t adapt itself to the new one.

Does it exist a way to dynamically change the content of a VBA code that point to a specific cell in a sheet?

Thank you for your valuable help