Results 1 to 2 of 2
  1. #1
    drdrew1469
    Guest

    Sorting Cosolidation Code (2000 SR1)

    Please advise as how to sort data that is being consolidated via vba code. I have the following which works great except I need for the first to sort into A2 and on and the second in B2and on, etc. Any help is greatly appreciated.

    Selection.Consolidate Sources:="'[19*.xls]Sheet1'!R3C3", Function:=xlCount _
    , TopRow:=False, LeftColumn:=False, CreateLinks:=True

    Selection.Consolidate Sources:="'[19*.xls]Sheet1'!R5C2", Function:=xlCount _
    , TopRow:=False, LeftColumn:=False, CreateLinks:=True

    Selection.Consolidate Sources:="'[19*.xls]Sheet1'!R14C9", Function:=xlCount _
    , TopRow:=False, LeftColumn:=False, CreateLinks:=True

    Drew

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Cosolidation Code (2000 SR1)

    I am not sure if this is what you want, but it may point you in the right direction:

    <pre>Sub Macro1()
    Worksheets("Sheet2").Range("A1").Consolidate _
    Sources:=Array("Sheet1!R1C5:R6C5", "Sheet1!R1C6:R6C7"), _
    Function:=xlSum

    Worksheets("Sheet2").Range("A1").CurrentRegion.Sor t Key1:=Range("A1"), _
    Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End Sub
    </pre>


    Instead of using the "Selection" object, I explicitly refer to the address for the consolidate output. This makes it easier for the sort command line. The Key property let you define by which column you want to do the sort.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •