Can't get Excel to consolidate

[Mitchys]

G'day,

Here is my code so far:

Private Sub CommandButton1_Click()
'   Goto the cell above where the data needs to be placed
    Application.Goto Reference:="start_consol"
'   Consolidate from the Budget worksheet
    Application.CutCopyMode = False
    ActiveCell.Offset(1, 0).Consolidate Sources:=Array("Budget!R5C11:R95C19") _
        , Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
End Sub

When I click the command button in the worksheet, nothing happens (even though the hourglass appears for a fraction of a second). No error message is displayed either.

Is there something that I'm doing wrong?

Thanks,

Mitchys

 

[NdNoviceHlp]

Don't know why but this works. Dave

Private Sub CommandButton1_Click()
'   Goto the cell above where the data needs to be placed
    Application.Goto Reference:="start_consol"
'   Consolidate from the Budget worksheet
    ActiveCell.Offset(1, 0) = vbNullString
    Application.CutCopyMode = False
    ActiveCell.Offset(1, 0).Consolidate Sources:=Array("Sheet1!R1C1:R1C4") _
        , Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
End Sub

edit: change your range and sheet references back to what you had

 

[Mitchys]

G'day Dave,

I finally found out last night why it wasn't working, but forgot to reply back here.

In the past, whenever I have manually consolidated figures, I have usually performed it in a blank sheet. If I had to re-condolidate figures, I deleted the first series (i.e. to do it in the same worksheet). Here was the key that I had missed - my code was working fine, but Excel wouldn't consolidate in the range I wanted while there were cells > 0. So, I had to clear the cells first, then my code worked. Over yesterday I tweaked my code somewhat and have come up with a short procedure that works:

Private Sub CommandButton1_Click()
'   Ensure data on worksheet is cleared
    Range("consol_data").Clear
'   Consolidate from the Budget worksheet
    Application.CutCopyMode = False
    Worksheets("Lead Schedule").Range("staff").Consolidate Sources:=Array("Budget!R5C11:R95C20") _
        , Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
    Range("staff").Select
End Sub

I haven't yet tried your code, but will respond once I have.

Thanks for your reply,

Mitch