Results 1 to 3 of 3

Thread: sum data of multiple sheets
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2019
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default sum data of multiple sheets

    hi, experts

    i have two sheets contain data sheet1 begins from range a5: e and sheet2 begins from a4:g i need code sum data the sheet1 from b5:e with sheet2 from c4:g and the result show in sheet1 from L5:O (NOTE: the topics header sheet1 from a4:e and sheet2 from a3:g

  2. #2
    Board Regular
    Join Date
    Oct 2018
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sum data of multiple sheets

    this sounds like homework, but when you say a5: e, are you talking a5:e5 or some other range.

  3. #3
    Board Regular
    Join Date
    May 2019
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sum data of multiple sheets

    Quote Originally Posted by LlebKcir View Post
    this sounds like homework, but when you say a5: e, are you talking a5:e5 or some other range.
    i ment range a5: a1000 or 2000 when i add data witout specefic noumbers and this is my code but it doesn't work well
    it appers data just the columns of text witout the column of value


    Code:
    Sub AnyThing()        Dim lastrow_1 As Long, counter As Long
            Dim lastrow_2 As Long, key As Variant
            Dim sh1 As Worksheet, sh2 As Worksheet
            Dim rng1, rng2 As Range, p As Variant
            Dim dict As Object
        Set sh1 = Sheets("SHEET1")
        Set sh2 = Sheets("SHEET2")
        sh2.Range("I3").Resize(1000, 3).ClearContents
        
        lastrow_1 = sh1.Cells(sh1.Rows.Count, "B").End(3).Row
        lastrow_2 = sh1.Cells(sh2.Rows.Count, "C").End(3).Row
        Set rng1 = sh1.Range("B3:E" & lastrow_1)
        Set rng2 = sh2.Range("C2:E" & lastrow_2)
        Set dict = CreateObject("Scripting.Dictionary")
    
    
        For Each p In rng1.Columns(2).Cells
            If Not dict.Exists(p.Value & "," & p.Offset(, 1)) Then
                dict.Add p.Value & "," & p.Offset(, 1), p.Offset(, 2)
            Else
                dict(p.Value & "," & p.Offset(, 1)) = _
                dict(p.Value & "," & p.Offset(, 1)) + p.Offset(, 2)
            End If
        Next p
       '===============================
           For Each p In rng2.Columns(2).Cells
            If Not dict.Exists(p.Value & "," & p.Offset(, 1)) Then
                dict.Add p.Value & "," & p.Offset(, 1), p.Offset(, 2)
            Else
                dict(p.Value & "," & p.Offset(, 1)) = _
                dict(p.Value & "," & p.Offset(, 1)) + p.Offset(, 2)
            End If
        Next p
               
        '==============================
       
       counter = 2
        With sh2
            For Each key In dict.Keys
                 counter = counter + 1
                .Cells(counter, "K").Resize(1, 2) = Split(key, ",")
                .Cells(counter, "O") = dict(key)
    
    
            Next key
            
        End With
    dict.RemoveAll: Set dict = Nothing
    Set sh1 = Nothing: Set sh2 = Nothing
    Set rng1 = Nothing: Set rng2 = Nothing
    End Sub
    Last edited by abdelfattah; Sep 13th, 2019 at 08:20 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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