Consolidation macro question...

[drdrew1469]

I have created the following macro to retrieve data from many excel spreadsheets (all the same layout). My problem is that when it retrieves the data, creates a link and puts the value in the active cellrange, it doesn't account for blank cells and jambs them all together, i.e. if retreiving 5 spreadsheets, it uses A1 - A3 instead of A1=#, A2=#, A3=blank, A4=blank, A5=#. Thant means I have to check the path (links) across my "consolidated data" to make sure the cells line up and if they don't, it's a mess to re-arrange all those cells. Any help is greatly appreciated.

Thanks,

Drew


With ActiveSheet.Outline
.AutomaticStyles = False
.SummaryRow = xlAbove
.SummaryColumn = xlLeft
End With

Range("A2").Select
Selection.Consolidate Sources:="'C:Path[*.xls]Sheet1'!R3C3", Function:=xlCount _
, TopRow:=False, LeftColumn:=False, CreateLinks:=True

Range("B2").Select
Selection.Consolidate Sources:="'C:Path[*.xls]Sheet1'!R5C2", Function:=xlCount _
, TopRow:=False, LeftColumn:=False, CreateLinks:=True

Range("C2").Select
Selection.Consolidate Sources:="'C:Path[*.xls]Sheet1'!R18C9", Function:=xlCount _
, TopRow:=False, LeftColumn:=False, CreateLinks:=True

Selection.ClearOutline

Range("A1").Select

End Sub