Results 1 to 5 of 5

Thread: Append Table data to another table

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0

    Append Table data to another table

    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
    Attached Files Attached Files

  2. #2
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0
    Can anyone help on this pls? Thanks

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Give this a try..

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

    Code:
    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
    Last edited by Admin; 08-09-2017 at 05:36 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0
    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.

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Quote Originally Posted by jeremiah_j2k View Post
    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.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Use SUMIFS on pivot table data
    By malta1 in forum Excel Help
    Replies: 1
    Last Post: 02-18-2014, 10:55 AM
  2. Sorting Data In An MS-Access Table Using SQL
    By Ringhal in forum Access Help
    Replies: 10
    Last Post: 09-26-2013, 11:41 AM
  3. Transpose A Column Of Data In To A Table
    By gunjan.nasit in forum Excel Help
    Replies: 4
    Last Post: 05-20-2013, 12:33 AM
  4. Excluding Records of one Table from the Other Table
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  5. Upload Excel Data to SQL Table
    By littleiitin in forum Excel and VBA Tips and Tricks
    Replies: 3
    Last Post: 08-22-2012, 11:02 AM

Posting Permissions

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