Consolidate

[Jimmy0306]

I have recorded a macro to consolidate my data and it works well on my machine, but the code looks for the Workbook to be on my Desktop

Range("A1").Select
    Selection.Consolidate Sources:= _
        "'C:\Documents and Settings\My Machine\Desktop\[Consolidate.xls]Data'!R1:R65536" _
        , Function:=xlAverage, TopRow:=True, LeftColumn:=True, CreateLinks:= _
        False

How do I change the code that different people can use the Workbook?

[JBeaucaire]

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