Copy values between ranges with the same name in different workbooks

[Olbap]

Hi,

I am trying to copy just the values from one range in one file to another file with the same range name, I wonder if this is at the origin of the "subscript out of range" error message I get.

Here is the code, where it stucks is in red:


Sub UpdateToFinalFile()
Dim ws As Worksheet
Dim cl As Range
Dim finalfile As String
Dim thisWB As String
Dim clad As String

finalfile = Range("finalfile").value
thisWB = ActiveWorkbook.Name
Workbooks.Open Filename:=finalfile
Workbooks(thisWB).Activate
For Each ws In Worksheets
If Left(ws.Name, 2) = "T_" Then

For Each cl In Workbooks(thisWB).Worksheets(ws.Name).Range("Data").Cells
clad = cl.Address
If cl.value <> "" And cl.value <> Workbooks(finalfile).Worksheets(ws.Name).Cells(cl.Row, cl.Column).value Then
Workbooks(finalfile).Worksheets(ws.Name).Range(clad).value = cl.value
End If
Next cl
Workbooks(thisWB).Worksheets("Contents").Cells(30, 1).value = ws.Name & " OK"
End If
Next ws
End Sub


Each range "Data" is specific to each sheet and my files are located in different drives.
P

 

[Stephen_Tsui]

Olbap:

Need to clear the path of finalfile
Try below code

If cl.value <> "" And cl.value <> Workbooks(Dir(finalfile)).Worksheets(ws.Name).Cells(cl.Row, cl.Column).value Then

 

[Olbap]

Thanks a lot Stephen! that worked very well.

Olbap