Change link formula in excel sheet by Macro

[wkhan]

Hi,

I have 01 Jan 21 name excel file, Sheet 1, data in Cell A1 which is 1 in Number
I have 02 Jan 21 name excel file , Sheet 1, data in Cell A1 which is 2 in Number

In Excel book 3 sheet name 01 Jan 21. i have created link in Excel book 3 from excel file name 01 jan 21 sheet 1 cell A1 or the formula is ='C:\Users\abc\Desktop\[01 jan 21.xlsx]Sheet1'!A1. showing result number 1 in cell A1 of Excel Book 3

if i come on 02 Jan 21 i create copy of 01 Jan 21 sheet in excel book 3 and change name to 02 Jan 21 in Excel Book 3 and i need macro button to change the link source from 01 Jan 21 to 02 Jan 21 OR it should edit formula text from 'C:\Users\abc\Desktop\[01 jan 21.xlsx]Sheet1'!A1 to 'C:\Users\abc\Desktop\[02 jan 21.xlsx]Sheet1'!A1

 

[Fluff]

Duplicate to: Change link formula in excel sheet by Macro

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.