Change fixed path to variable path in Consolidate Data Function

    Hi all,


    Long time lurker but first time posting...
    I hope someone is able to help me out.


    I have one workbook which consolidates data from various other workbooks in order to provide a nice and clean overview...
    One of the ''secondary'' workbooks could have variable names and/or folderpaths...


    I recorded a macro during consolidating data from a secondary workbook to the main overview....


    Code
    Sub Macro4()




    Selection.Consolidate Sources:= _
        "'G:\CHS Logging\EQUIPMENT AND MAINTENANCE\003 - MCR (RCT_PTS) Information\RCT Project\current version\[RCT_PTC Selection RC3.xlsm]RCT_Summary'!R51C16:R79C24" _
        , Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=True
End Sub


    All is working fine, however I would like to be able to direct the routine to the variable filepath which is mentioned in cel E9 of the Main worksheet.
    I've searched the net but can not get it to work for some reason...


    Does anyone know a proper compact solution to this for me?


    Many thanks


    Alex

    You can use the & to concatenate range values. For example if cell E9 contains the filepath, F9 contains the workbook name, G9 contains the sheet name and H9 contains the data range you could use = "'" & Range("E9").value & "[" & Range("F9").value & "]" & Range("G9").value & "'!" & Range("F9").value


    Hope this helps.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login