Sorry for double-post, my bad

drdrew1469

New Member
Joined
May 5, 2002
Messages
5
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
This message was edited by drdrew1469 on 2002-05-06 09:12
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top