CODEWITHSUNDEEP
vbaexcel
Mikz
Mikz

Reputation: 591

sum acorss sheets in vba

I have two Sheets , sheet1 called BW, and sheet 2 called reasons.

In sheet 1 I have columns AD till AW, which has to be added individually. i have table in sheet2, where I have listed the column names of AD till AW . in the second column of sheet 2 i Need the sum of column AD till AW. I tried a code like below

Sub sum()

    Dim BWlRow As Long, CWlRow As Long, i As Long
    Dim Sformula As String
    Dim wsBW As Worksheet, wsCW As Worksheet

    Set wsBW = Sheets("BW"): Set wsCW = Sheets("Reasons")

    BWlRow = wsBW.Cells(wsBW.Rows.Count, "A").End(xlUp).Row
    CWlRow = wsCW.Cells(wsCW.Rows.Count, "A").End(xlUp).Row


    For i = 30 To 47 '
        Sformula = "=SUM(AD2, BW)"


      With wsBW
    With .Range(.Cells(2, i), .Cells(BWlRow, i))
        .Formula = Sformula
        .Value = .Value
    End With
End With
    Next i
End Sub

I am not able to succeed with this.

I have like this in sheet 1 from column AD till AW I Need to have this result.

Upvotes: 0

Views: 40

Answers (1)

Mrig
Mrig

Reputation: 11702

Assumptions:

  1. Sheets("BW") is the sheet why you have data from Column AD-AW
  2. In Sheets("Reasons") Column A you have listed down the headers of Sheets("BW") Column AD-AW in same order, so there's no need to match for the headers
  3. You want sum of corresponding Sheets("Reasons") Column A items in Sheets("Reasons") Column B

  4. Last row of each sheet is on the basis of number of records in Column A of both sheets

    Sub sum()
    Dim BWlRow As Long, CWlRow As Long, i As Long
    Dim Sformula As String
    Dim wsBW As Worksheet, wsCW As Worksheet

    Set wsBW = Sheets("BW"): Set wsCW = Sheets("Reasons")

    BWlRow = wsBW.Cells(wsBW.Rows.Count, "A").End(xlUp).Row
    CWlRow = wsCW.Cells(wsCW.Rows.Count, "A").End(xlUp).Row

    Sformula = "=SUM(INDIRECT(""BW!"" & CELL(""address"",OFFSET(AD$2,0,ROW()-2)) & "":"" & CELL(""address"",OFFSET(AD$10,0,ROW()-2))))"

    With wsCW
        With .Range(.Cells(2, 2), .Cells(CWlRow, 2))
            .Formula = Sformula
            .Value = .Value
        End With
    End With
End Sub

Upvotes: 2

Related Questions