Consolidate macro then I get #REF formula error when sharing workbook

Instead of saving the master as an excel file, save it as a CSV file.

saving as csv doesn't work because they need them in excel

saving as values doesn't work either because they need to manipulate the file and use the formulas

** The formulas refer to another worksheet hide in the woorkbook, but when I consolidate and save the vlookup formula refers to the location of the file in my computer, the original formula refers to a table in a hidden sheet
As I already mention I can not copy the formula down the column because some cells are text and not formulas

DGagnon yes we are close, how can I make that in VB so I can integrate it in the macro when I consolidate my worrkbooks?

Since the root will be changing everytime I consolidate it need to be with variables, can it be done?
I already have one called root that gives me the root where the file is: C:\Users\JJI\AppData\Local\Temp\notesE69A9C\
Also have other variable with the workbookname: HC MARCH 1.xlsx

I did a macro record and got this code:

Cells.Replace What:= _
"'C:\Users\FZN5LB\Documents\Juanjo Data\HUM025 - Headcount\2012 03\Manpower Marzo\Manpower HC Ramos Marzo.xlsx'!Salary" _
, Replacement:="Salary", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Originally Posted by DGagnon

try using this:

Cells.Replace What:= _
"'" & ActiveWorkbook.Path & "[" & ActiveWorkbook.Name & "]" _
, Replacement:="'", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Almost there, since I'm copying and pasteing from diferent workbooks the ActiveWorkbook.Name is not working right
Take a look at my code

nombre2 = Root & "\" & s
            Workbooks.Open (nombre2)
            librocopy = ActiveWorkbook.Name
            i = 5
            While Workbooks(librocopy).Worksheets("Manpower").Cells(i, 1).Value <> NullString
                i = i + 1 'cuento los rengones a copiar
            Wend
            rangoCopy = "A5:AE" & i - 1
            Workbooks(librofinal).Activate
            Workbooks(s).Worksheets("Headcount").Range(rangoCopy).Copy _
            Destination:=Workbooks(librofinal).Worksheets("Manpower").Range("A" & a)
            cont = a
            cont = cont + (i - 5)

where librofinal will be my consolidated and librocopy is the workbook I open to copy info to librofinal
this is a loop that goes through all the files in a folder

Thanks for your help. its working now