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....

    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


  • 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!