Advanced Consolidation in vba

Question

I have created a macro to combine two different sheets and apply various formulas. Now, I want to perform a consolidation. I am rather new at VBA so I dont quite know the capabilities of it. Is it possible to perform a consolidation (to a specific column , lets say column D) and select the columns that you want to sum after consolidation, keep the same or average? In more detail, lets say, I want the first column to stay the same, the second to be summed, the third to be averaged, the forth is the consolidation column, the fifth to be summed, so on and so forth! I am providing the code that I have built so far, although its totally irrelevant with the consolidation process

Sub Budget_Report()

Application.ScreenUpdating = False

Worksheets.Add.Name = "Budget_Report"

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim lastrow1 As Long
Dim lastrow2 As Long
Dim lastrow3 As Long
Dim lastrow4 As Long
Dim i As Long
Dim cell1 As Range
Dim cell2 As Range
Set ws1 = Worksheets("SAPBW_DOWNLOAD")
Set ws2 = Worksheets("Total_Lane_Level")
Set ws3 = Worksheets("Budget_Report")
lastrow1 = ws2.Range("A" & Rows.Count).End(xlUp).Row
lastrow2 = ws1.Range("A" & Rows.Count).End(xlUp).Row

With ws1
    For i = lastrow2 To 2 Step -1
        If .Cells(i, "K").Value = "Result" Then
            .Rows(i).EntireRow.Delete
        End If
    Next i
End With

lastrow3 = ws1.Range("A" & Rows.Count).End(xlUp).Row

ws1.Range("AD2:AD" & lastrow3).Formula = "=C2&""-""&H2&""-""&B2&""-""&F2&""-""&D2&""-""&I2"

ws1.Range("AE2:AE" & lastrow3).Formula = "=INDEX('Map'!H:H,MATCH('SAPBW_DOWNLOAD'!AD2,'Map'!G:G,FALSE))"

ws3.Range("A1").Value = "Transportation Planning Point"
ws3.Range("B1").Value = "Origin"
ws3.Range("C1").Value = "Destination"
ws3.Range("D1").Value = "T-Lane"
ws3.Range("E1").Value = "Full FY Total $M Cost"
ws3.Range("F1").Value = "Total FY MSU"
ws3.Range("G1").Value = "Monthly Cost FCST"
ws3.Range("H1").Value = "Monthly Vol FCST"
ws3.Range("I1").Value = "Monthly $/SU FCST"
ws3.Range("J1").Value = "Total Transportation Costs"
ws3.Range("K1").Value = "Currency Rate (to dolars)"
ws3.Range("L1").Value = "SU"
ws3.Range("M1").Value = "Monthly Cost"
ws3.Range("N1").Value = "Monthly Vol"
ws3.Range("O1").Value = "Monthly $/SU"
ws3.Range("P1").Value = "Monthly Cost"
ws3.Range("Q1").Value = "Monthly Vol"
ws3.Range("R1").Value = "Monthly $/SU"
ws3.Range("S1").Value = "%"                  ''''
ws3.Range("U1").Value = "Total FY Loads"
ws3.Range("V1").Value = "Total FY Pall/Load"
ws3.Range("W1").Value = "Monthly #Loads FCST"
ws3.Range("X1").Value = "Pal/Load FCST"
ws3.Range("Y1").Value = "Shipments"
ws3.Range("Z1").Value = "Pallets"
ws3.Range("AA1").Value = "Monthly #Loads"
ws3.Range("AB1").Value = "Pal/Load "

ws3.Range("A2:A" & lastrow2).Value = ws1.Range("C2:C" & lastrow2).Value
ws3.Range("B2:B" & lastrow2).Value = ws1.Range("B2:B" & lastrow2).Value

ws3.Range("C2:C" & lastrow2).Value = ws1.Range("F2:F" & lastrow2).Value
ws3.Range("D2:D" & lastrow2).Value = ws1.Range("AE2:AE" & lastrow2).Value

ws3.Range("J2:J" & lastrow2).Value = ws1.Range("W2:W" & lastrow2).Value
ws3.Range("J2:J" & lastrow2).NumberFormat = "0.00"

ws3.Range("L2:L" & lastrow2).Value = ws1.Range("S2:S" & lastrow2).Value
ws3.Range("L2:L" & lastrow2).NumberFormat = "0.00"

ws3.Range("Y2:Y" & lastrow2).Value = ws1.Range("V2:V" & lastrow2).Value
ws3.Range("Y2:Y" & lastrow2).NumberFormat = "0.00"

ws3.Range("Z2:Z" & lastrow2).Value = ws1.Range("L2:L" & lastrow2).Value
ws3.Range("Z2:Z" & lastrow2).NumberFormat = "0.00"

lastrow4 = ws3.Range("A" & Rows.Count).End(xlUp).Row

ws3.Range("E2:E" & lastrow4).Formula = "=INDEX('Total_Lane_Level'!M:M,MATCH('Budget_Report'!D2,'Total_Lane_Level'!B:B,FALSE))"
ws3.Range("E2:E" & lastrow4).NumberFormat = "0.00"

ws3.Range("F2:F" & lastrow4).Formula = "=INDEX('Total_Lane_Level'!F:F,MATCH('Budget_Report'!D2,'Total_Lane_Level'!B:B,FALSE))"
ws3.Range("F2:F" & lastrow4).NumberFormat = "0.00"

ws3.Range("G2:G" & lastrow4).Formula = "=E2/12"
ws3.Range("G2:G" & lastrow4).NumberFormat = "0.00"

ws3.Range("H2:H" & lastrow4).Formula = "=F2/12"
ws3.Range("H2:H" & lastrow4).NumberFormat = "0.00"

ws3.Range("I2:I" & lastrow4).Formula = "=G2/H2"
ws3.Range("I2:I" & lastrow4).NumberFormat = "0.00"

For Each cell1 In ws3.Range("K2:K" & lastrow4)
    If cell1.Offset(0, -8).Value = "ROMANIA" Then
        cell1.Value = 4.24790790535661
    ElseIf cell1.Offset(0, -8).Value = "WEST BANK & GAZ" Or cell1.Offset(0, -8).Value = "ISRAEL" Then
        cell1.Value = 1
    Else
        cell1.Value = 1.07174
    End If
Next cell1
ws3.Range("K2:K" & lastrow4).NumberFormat = "0.00"

For Each cell2 In ws3.Range("M2:M" & lastrow4)
    If cell2.Offset(0, -10).Value = "ROMANIA" Then
        cell2.Value2 = cell2.Offset(0, -3).Value2 / cell2.Offset(0, -2).Value2
    Else
        cell2.Value2 = cell2.Offset(0, -3).Value2 * cell2.Offset(0, -2).Value2
    End If
Next cell2

ws3.Range("N2:N" & lastrow4).Formula = "=L2/1000"
ws3.Range("N2:N" & lastrow4).NumberFormat = "0.00"

ws3.Range("O2:O" & lastrow4).Formula = "=M2/L2"
ws3.Range("O2:O" & lastrow4).NumberFormat = "0.00"

ws3.Range("P2:P" & lastrow4).Formula = "=G2-M2"
ws3.Range("P2:P" & lastrow4).NumberFormat = "0.00"

ws3.Range("Q2:Q" & lastrow4).Formula = "=H2-N2"
ws3.Range("Q2:Q" & lastrow4).NumberFormat = "0.00"

ws3.Range("R2:R" & lastrow4).Formula = "=I2-O2"
ws3.Range("R2:R" & lastrow4).NumberFormat = "0.00"

ws3.Range("U2:U" & lastrow4).Formula = "=INDEX('Total_Lane_Level'!J:J,MATCH('Budget_Report'!D2,'Total_Lane_Level'!B:B,FALSE))"
ws3.Range("U2:U" & lastrow4).NumberFormat = "0.00"

ws3.Range("V2:V" & lastrow4).Formula = "=INDEX('Total_Lane_Level'!I:I,MATCH('Budget_Report'!D2,'Total_Lane_Level'!B:B,FALSE))"
ws3.Range("V2:V" & lastrow4).NumberFormat = "0.00"

ws3.Range("W2:W" & lastrow4).Formula = "=U2/12"
ws3.Range("W2:W" & lastrow4).NumberFormat = "0.00"

ws3.Range("X2:X" & lastrow4).Formula = "=V2"
ws3.Range("X2:X" & lastrow4).NumberFormat = "0.00"

ws3.Range("AA2:AA" & lastrow4).Formula = "=Y2"
ws3.Range("AA2:AA" & lastrow4).NumberFormat = "0.00"

ws3.Range("AB2:AB" & lastrow4).Formula = "=Z2/Y2"
ws3.Range("AB2:AB" & lastrow4).NumberFormat = "0.00"

Application.ScreenUpdating = True

End Sub

You can indent your code to make it easier to read. For your luck there is a [website for it, Rubberduck VBA](http://rubberduckvba.com/indentation). — danieltakeshi, Oct 17 '17 at 13:18
Do you mean consolidation as in finance system? Could you please specify what is an advanced consolidation? — danieltakeshi, Oct 17 '17 at 13:49
well, I want to be able to select whether I want to sum a column, or average or keep the same value (i.e. if a column has the value "Hey" I want to keep the same value for the consolidated rows in this specific column). — Pericles Faliagas, Oct 17 '17 at 13:53
There are many ways to achieve this, you can use: option buttons, if statements, userforms, Case — danieltakeshi, Oct 17 '17 at 15:01
Well I wanted to see if there was an option to implement what I described to a consolidation formula so as to avoid long codes ! But nevertheless thank you for helping !! — Pericles Faliagas, Oct 17 '17 at 15:03
Well I want the consolidation too ... I understand what you mean but my main goal is the consolidation — Pericles Faliagas, Oct 17 '17 at 15:08

Advanced Consolidation in vba

0 Answers0