Changing file name in cell based on another cells' values

[aismail83]

Hi,

I have around 400 workbooks are named "1.xlsx, 2.xlsx....400.xlsx" in same folder, because they have merged cells I can't do VLOOKUP, so I need to pick this data manually by

='C:\Users\Documents\2023\Data\RawData-Mar\[1.xlsx]Sheet1'!$C$4

so, I need to file name be changed to match serial number of that cell, example

S/N

A	B
S/N	Customer name
1	='C:\Users\Documents\2023\Data\RawData-Mar\[=a1.xlsx]Sheet1'!$C$4
2	='C:\Users\Documents\2023\Data\RawData-Mar\[=a2.xlsx]Sheet1'!$C$4
3	='C:\Users\Documents\2023\Data\RawData-Mar\[=a3.xlsx]Sheet1'!$C$4
4	='C:\Users\Documents\2023\Data\RawData-Mar\[=a4.xlsx]Sheet1'!$C$4

the problem here that i have 8 more fields from each workbook, and when I tried this I have to manually confirm the file location for each field.

any ideas?

 

[James006]

Hi,
You can test and adapt following

Sub Rename()
' With Current File Name in cell A1  AND  New File Name in cell A2 :
 Name ActiveSheet.Range("A1") As ActiveSheet.Range("A2")
End Sub

 

[aismail83]

Hi,
You can test and adapt following

Sub Rename()
' With Current File Name in cell A1  AND  New File Name in cell A2 :
 Name ActiveSheet.Range("A1") As ActiveSheet.Range("A2")
End Sub

thanks James,

any tutorial to implement this?

 

[James006]

Tutorial ???
Just make a Test ...
The comment shown in Green explains How to Use the Macro ....