Sum 30 columns on sheet01 and write results to sheet 2

[Kumarkusa]

Hi ,
We have a data sheet01 which has got 30 columns, for all columns the named ranges are defined. The number of rows change, so the size of named range columns is defined dynamically.

In sheet02 the target columns named rages are also defined based on the size of sheet01 ranges.

Sheet01
COLUMN NAMED RANGE
Column1 = Source_Column1
Column2 = Source_Column2
Column3 = Source_Column3
Column4 = Source_Column4
....


Sheet02COLUMN NAMED RANGE
Column1 = Target_Column1

We need a sum of sheet01 named ranges and place the result in sheet02 named range.


Target_Column1 = Source_Column1 + Source_Column2 + Source_Column3 + Source_Column4..............


How can we achieve it in VBA

 

[SirJB7]

Hi, Kumarkusa!
Try this:

    Range("Target_Column1").Value = _   
        Application.WorksheetFunction.Sum(Range("Source_Column1").Cells, _
            Range("Source_Column2").Cells, Range("Source_Column3").Cells, _
            Range("Source_Column4").Cells)

Or:

    Dim I As Integer, A As String
    Range("Target_Column1").Value = 0
    For I = 1 To 4
        Range("Target_Column1").Value = Range("Target_Column1").Value + _
        Application.WorksheetFunction.Sum(Range("Source_Column" + CStr(I)).Cells)
    Next I

Regards!

 

[Kumarkusa]

Hi, Kumarkusa!
Try this:

    Range("Target_Column1").Value = _  
        Application.WorksheetFunction.Sum(Range("Source_Column1").Cells, _
            Range("Source_Column2").Cells, Range("Source_Column3").Cells, _
            Range("Source_Column4").Cells)

Or:

    Dim I As Integer, A As String
    Range("Target_Column1").Value = 0
    For I = 1 To 4
        Range("Target_Column1").Value = Range("Target_Column1").Value + _
        Application.WorksheetFunction.Sum(Range("Source_Column" + CStr(I)).Cells)
    Next I

Regards!

Thanks for providing the solutions SirJB7.

The 1st code is just giving the sum of all source columns in all target columns...
ie Target_Column1 = 100 (All cells in column) ...Sum of the source ranges..i replaced Cells by Value in Source side ...but it didnt work...

The 2nd code will not work for my req...as the source range names are not in sequence...I am sorry i did not provide this information earlier...

Appreciate your time

 

[SirJB7]

Hi, Kumarkusa!

I'm not sure if I understood:
a) the 2nd code doesn't work for you because of the range names?
b) the 1st code doesn't work after you changed something?

For a) try this:

    Dim I As Integer, J As Integer, A As String
    Dim sRng(100) As String
    With ActiveWorkbook
        For I = 1 To .Names.Count
            With .Names(I)
               ' exclude _FilterDatabase, _Print-Area, special names
                If Left(.Name, 1) <> "_" Then
                    If <custom filtering condition> Then
                        J = J + 1
                        sRng(J) = .Name
                    End If
                End If
            End With
        Next I
    End With
    Range("Target_Column1").Value = 0
    For I = 1 To J
        If sRng(I) <> "" Then
            Range("Target_Column1").Value = Range("Target_Column1").Value + _
                Application.WorksheetFunction.Sum(Range(sRng(I)).Cells)
        End If
    Next I

If your custom filtering condition is too complex, you have 2 choices, either rename the involved ranges to a name beginning with a non used letter or assign then manually.

Regards!