Append Table data to another table

[jeremiah_j2k]

Hello Team,


I'm having some challenges on how to copy the data from my 2 tables on PivotDaily sheet to tables on DataSummary sheet. PivotDaily tables can have as much data as possible and I need to automate the process of copy and paste and ensure that I do not append data from the same date (Closed Date) twice (Please see the attached file). Hope anyone can assist me on this. Any help would be very much appreciated.


Thanks a mil

[jeremiah_j2k]

Can anyone help on this pls? Thanks

[Admin]

Hi

Give this a try..

Edit: I noticed Closed date on Col M on PivotDaily sheet is not true dates. Make it true dates.

Option Explicit

Sub kTest()
    
    Dim k(1), kk(1), dic As Object, i As Long
    Dim s   As String, n(1) As Long, c As Long, j As Long
    Dim kkk0, kkk1
    
    With Worksheets("DataSummary")
        k(0) = .[b1].CurrentRegion.Resize(, 8).Value 'assume no data in col A
        k(1) = .[k1].CurrentRegion.Value 'assume no data in col J
    End With
    
    With Worksheets("PivotDaily")
        kk(0) = .[b5].CurrentRegion.Resize(, 8).Value 'assume no data in col A
        kk(1) = .[k5].CurrentRegion.Value 'assume no data in col J
    End With
    
    ReDim kkk0(1 To UBound(k(0), 1) + UBound(kk(0), 1), 1 To UBound(k(0), 2))
    ReDim kkk1(1 To UBound(k(1), 1) + UBound(kk(1), 1), 1 To UBound(k(1), 2))
    
    Set dic = CreateObject("scripting.dictionary"): dic.comparemode = 1
    
    For j = 0 To 1
        For i = 1 To UBound(k(j), 1)
            If j = 0 Then
                s = k(j)(i, 1) & k(j)(i, 2) & k(j)(i, 3) & k(j)(i, 4) & k(j)(i, 5) & k(j)(i, 8)
            Else
                s = vbNullString
                For c = 1 To UBound(k(j), 2)
                    s = s & "|" & k(j)(i, c)
                Next
            End If
            For c = 1 To UBound(k(j), 2)
                If j = 0 Then
                    kkk0(i, c) = k(j)(i, c)
                Else
                    kkk1(i, c) = k(j)(i, c)
                End If
            Next
            dic.Item(s) = i
        Next
        n(j) = i - 1
    Next
    
    For j = 0 To 1
        For i = 1 To UBound(kk(j), 1)
            If j = 0 Then
                s = kk(j)(i, 1) & kk(j)(i, 2) & kk(j)(i, 3) & kk(j)(i, 4) & kk(j)(i, 5) & kk(j)(i, 8)
            Else
                s = vbNullString
                For c = 1 To UBound(kk(j), 2)
                    s = s & "|" & kk(j)(i, c)
                Next
            End If
            If Not dic.exists(s) Then
                n(j) = n(j) + 1
                For c = 1 To UBound(kk(j), 2)
                    If j = 0 Then
                        kkk0(n(j), c) = kk(j)(i, c)
                    Else
                        kkk1(n(j), c) = kk(j)(i, c)
                    End If
                Next
                dic.Item(s) = i
            End If
        Next
    Next
    
    With Worksheets("DataSummary")
        .[b1].Resize(n(0), UBound(k(0), 2)).Value = kkk0
        .[k1].Resize(n(1), UBound(k(1), 2)).Value = kkk1
    End With
        
End Sub

[jeremiah_j2k]

Thanks a lot admin. I noticed that it only the data on the second table is being copied on succeeding clicks and no dup checking if data for the day has already has already been exported. Could be with the loop but cant figure out where to fix it in our code.

[Admin]

Thanks a lot admin. I noticed that it only the data on the second table is being copied on succeeding clicks and no dup checking if data for the day has already has already been exported. Could be with the loop but cant figure out where to fix it in our code.

As I told you that you have to convert those dates in first sheet into a true dates. It looks true dates, apparently it's not.