CODEWITHSUNDEEP
vbaexcel
Mario
Mario

Reputation: 13

How to merge two different tables and then create a new one with vba?

I am trying to merge two different tables on two different sheets and paste the results on the other table in another sheet?

For instance, SUM:

          sheet 1 ("A1") = 1
          sheet 2 ("A1") = 2

 PASTE to: sheet 3 ("A1") = 3

My tables are dynamic and big ("A1:D27"), I couldn't find a way to loop through each cell in order and paste to the next one?

I have tried to loop each cell but not working, I tried copy paste and add but not working with dynamic range.

Upvotes: 1

Views: 120

Answers (1)

Fuzzzzel
Fuzzzzel

Reputation: 1783

This function should do the trick:

  • Get the minimum row/column used in both sheets
  • Get the maximum row/column used in both sheets
  • Create the maximum used range from both sheets
  • Clear the third sheet where you need to enter the summed values
  • Cycle through the maximum used range, add the corresponding cell values and enter the result into the third sheet

This works for me in a test:

Sub SumValues()
    ' Get reference to the sheets
    Dim Sheet1 As Worksheet
    Set Sheet1 = ActiveWorkbook.Sheets(1)

    Dim Sheet2 As Worksheet
    Set Sheet2 = ActiveWorkbook.Sheets(2)

    Dim Sheet3 As Worksheet
    Set Sheet3 = ActiveWorkbook.Sheets(3)

    ' Find largest used range

    ' Sheet1
    FirstRowS1 = Sheet1.UsedRange.Rows(1).Row
    LastRowS1 = Sheet1.UsedRange.Rows(Sheet1.UsedRange.Rows.Count).Row
    FirstColS1 = Sheet1.UsedRange.Columns(1).Column
    LastColS1 = Sheet1.UsedRange.Columns(Sheet1.UsedRange.Columns.Count).Column

    ' Sheet2
    FirstRowS2 = Sheet2.UsedRange.Rows(1).Row
    LastRowS2 = Sheet2.UsedRange.Rows(Sheet2.UsedRange.Rows.Count).Row
    FirstColS2 = Sheet2.UsedRange.Columns(1).Column
    LastColS2 = Sheet2.UsedRange.Columns(Sheet2.UsedRange.Columns.Count).Column

    ' Largest used range is min rows to max rows and min columns to max columns
    ' Minimum row from both sheeets
    MinRow = 0
    If FirstRowS1 < FirstRowS2 Then
        MinRow = FirstRowS1
    Else
        MinRow = FirstRowS2
    End If

    ' Maximum row from both sheeets        
    MaxRow = 0
    If LastRowS1 > LastRowS2 Then
        MaxRow = LastRowS1
    Else
        MaxRow = LastRowS2
    End If

    ' Minimum column from both sheeets
    MinCol = 0
    If FirstColS1 < FirstRowS2 Then
        MinCol = FirstColS1
    Else
        MinCol = FirstColS2
    End If

    ' Maximum column from both sheeets
    MaxCol = 0
    If LastColS1 < LastRowS2 Then
        MaxCol = LastColS1
    Else
        MaxCol = LastColS2
    End If

    ' Clear Sheet3
    Sheet3.Cells.Clear

    ' Cycle through largest range that is used
    For Row = MinRow To MaxRow
        For Col = MinCol To MaxCol
            Sheet3.Cells(Row, Col).Value = Sheet1.Cells(Row, Col).Value + Sheet2.Cells(Row, Col).Value
        Next col
    Next Row

End Sub

Upvotes: 1

Related Questions