Vba rename file when cell of another workbook is updated

**mariec_06** · 12-29-2015, 08:54 AM

I am new in VBA and I have trouble to refer to proper range/cell.

I have a workbook "cargoes 2016" used as database with 3 worksheets. The main worksheet "recap" allocates a "ref" number (column "c") to each new entry formatted "2016XX001, 2016XX002......" and sorts the entry per this ref nr.

I have already a macro which creates automatically a workbook "calc sheet" for each new reference I enter in "cargoes 2016". This works perfectly.

I would like that each time I update the column "AI" of Cargoes 2016 / recap for a specific ref, my code goes in the specific folder to find the file whose name includes the related "ref" nr and rename this file adding at the end of the pre-formatted name the value of the "ai" column.

For example, if in Cargoes 2016 / recap, I input a value in the column "ai" of the ref "2016xx003", the code goes to the specific folder, find the workbook whose title includes 2016xx003 and add at the end the value of Cargoes 2016 / recap/column Ai for 2016xx003.....

here is the code I work on until now but obviously I don't know how to refer to the correct row/cell.....can someone help?

Sub RENAME_FILES()

Dim LR As Long, i As Long, Filename As String, OldName As String, NewName As String

LR = Workbooks("CARGOES 2016").Worksheets("RECAP").Range("C" & Rows.Count).End(xlUp).Row

On Error Resume Next
For i = 2 To LR
Directory = "C:\Users\mciavaldini.TAMOILCY\Documents\test\"

OldName = "CALC(P) - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("C" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("L" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("M" & i).Value & " + " & "CALC(S) - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("W" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("X" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("F" & i).Value & ".xlsm"

NewName = "CALC(P) - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("C" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("L" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("M" & i).Value & " + " & "CALC(S) - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("W" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("X" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("F" & i).Value & Format(Workbooks("CARGOES 2016").Worksheets("RECAP").Range("AI" & i).Value, "dd-mmm-yy") & ".xlsm"

Filename = Dir(Directory & OldName)

If Filename <> "" Then
   
   Name Filename As Directory & NewName
  End If
  
Next i
On Error GoTo 0
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long

LR = Workbooks("CARGOES 2016").Worksheets("RECAP").Range("C" & Rows.Count).End(xlUp).Row

For i = 2 To LR

If Not Intersect(Workbooks("CARGOES 2016").Worksheets("RECAP").Range("AI" & i), Target) Is Nothing And Workbooks("CARGOES 2016").Worksheets("RECAP").Range("AI" & i) <> "" Then

RENAME_FILES
      
End If

Next i
On Error GoTo 0

End Sub