macro to be modified

Hi All,

With google I developed something that forks for individual cell:
Sub PullValue()
Dim PATH, FILENAME, SHEETNAME, CELL

PATH = "J:\ISO\01_ISO\07_Audity wewnętrzne\2015\Karty niezgodności\"
FILENAME = Range("M5")
SHEETNAME = "DCT_ISOFOR_SC_9.02_2015"
CELL = "I8"

With Range("C5")
.Formula = "='" & PATH & "[" & FILENAME & "]" & SHEETNAME & "'!" & CELL & ""
.Value = .Value
End With

End Sub


I have unsuccessfully tried to develop macro that works dynamically for all column, so what I wish:
Sub PullValue()
Dim PATH, FILENAME, SHEETNAME, CELL

PATH = "J:\ISO\01_ISO\07_Audity wewnętrzne\2015\Karty niezgodności\"
FILENAME = Range("M5")
SHEETNAME = "DCT_ISOFOR_SC_9.02_2015"
CELL = "I8"

With Range("C5")
.Formula = "='" & PATH & "[" & FILENAME'(M5:M204 switched for relevant range C5:C204) & "]" & SHEETNAME & "'!" & CELL & ""
.Value = .Value
End With

End Sub


So the only dynamic should be FILENAME.
Could you please to provide me with modified macro/support?


Regards,
Marcin

All began with formulas.
There is the story


Regards,
Marcin

Originally Posted by Marcin4111

Dim PATH, FILENAME, SHEETNAME, CELL

PATH = "J:\ISO\01_ISO\07_Audity wewnętrzne\2015\Karty niezgodności\"
FILENAME = Range("M5")
SHEETNAME = "DCT_ISOFOR_SC_9.02_2015"
CELL = "I8"

With Range("C5")
.Formula = "='" & PATH & "[" & FILENAME & "]" & SHEETNAME & "'!" & CELL & ""
.Value = .Value
End With

End Sub

There are 4 factors required for simulation:
PATH - static
FILENAME - dynamic required
SHEET - static
CELL - static

Dynamic values for FILENAME are defined in Range("M5:M204")

Formula should cover Range("C5:C204")
.Formula = "='" & PATH & "[" & FILENAME 'dynamic values & "]" & SHEETNAME & "'!" & CELL & ""


I hope you understand my intentions.


Regards,
Marcin