You've mistitled this thread... "How to insert Desktop address as variable" is more like it...

Try this:
Option Explicit

Public Function DesktopAddress() As String
    DesktopAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
End Function


Sub Consolidation()

    Range("A1").Consolidate Sources:=DesktopAddress & "[Consolidate.xls]Data'!R1:R" & Rows.Count, _
        Function:=xlAverage, TopRow:=True, LeftColumn:=True, CreateLinks:=False

End Sub