I need help with this macro:
Sub Lookup_file_locate()
Dim FileName As String
Path = Worksheets("Item List").Cells(1, 1)
ChDir Path
Worksheets("Estimate (1)").Cells(10, 3).FormulaR1C1 = "= LOOKUP(R9C3,'Item List.xls'!C1,'Item List.xls'!C2)"
Worksheets("Estimate (1)").Cells(14, 3).FormulaR1C1 = "= LOOKUP(R9C3,'Item List.xls'!C1,'Item List.xls'!C3)"
Worksheets("Estimate (1)").Cells(16, 3).FormulaR1C1 = "= LOOKUP(R9C3,'Item List.xls'!C1,'Item List.xls'!C4)"
End Sub
I created it to up date a location of a look up table when the destination file or the Table is moved. Have it looking at a cell in a sheet in the workbook for the new path but my formulas don't seem to want to recognize it.
Any help would be much appreciated
Sub Lookup_file_locate()
Dim FileName As String
Path = Worksheets("Item List").Cells(1, 1)
ChDir Path
Worksheets("Estimate (1)").Cells(10, 3).FormulaR1C1 = "= LOOKUP(R9C3,'Item List.xls'!C1,'Item List.xls'!C2)"
Worksheets("Estimate (1)").Cells(14, 3).FormulaR1C1 = "= LOOKUP(R9C3,'Item List.xls'!C1,'Item List.xls'!C3)"
Worksheets("Estimate (1)").Cells(16, 3).FormulaR1C1 = "= LOOKUP(R9C3,'Item List.xls'!C1,'Item List.xls'!C4)"
End Sub
I created it to up date a location of a look up table when the destination file or the Table is moved. Have it looking at a cell in a sheet in the workbook for the new path but my formulas don't seem to want to recognize it.
Any help would be much appreciated