Consulting

Results 1 to 11 of 11

Thread: help adjusting code combine two tables from two sheets to another sheet

  1. #1

    help adjusting code combine two tables from two sheets to another sheet

    hi, experts

    actullay i searched in internet but i don't reach to any result
    let us get in subject i have three sheets the sheet1 contains table from a2: d15 of course the topics of headers begins a1: d1 and the same thing with sheet2 but the sheet3 from begin a2:e15 a2= numbering serials items here my problem when i transfer table1 from sheet1, to sheet3 it's normal but when i transfer table2 to sheet3 it also repeats topics of headers i don't need it
    my images about file table1=sheet1,table2=sheet2,table3=sheet3

    and this is my code
    HTML Code:
    Sub CopyTables()
        sheet1.ListObjects("Table1").Range.Copy _     
              Destination:=sheet3.Range("B1")
    
        sheet1.ListObjects("Table2").Range.Copy _        
              Destination:=sheet3.Range("B4")
    End Sub
    Attached Images Attached Images

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,638
    Read a book on VBA for beginners.
    Follow a course for VBA-beginners.

  3. #3
    Quote Originally Posted by snb View Post
    Read a book on VBA for beginners.
    Follow a course for VBA-beginners.
    i just need adjusting this code is there any way

  4. #4
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Sheet1.Cells(1).CurrentRegion.Offset(1).Copy _
        Sheet3.Cells(1).ListObject.Range.Columns(2).Find("*", , , , , 2).Offset(1)
    Sheet2.Cells(1).CurrentRegion.Offset(1).Copy _
        Sheet3.Cells(1).ListObject.Range.Columns(2).Find("*", , , , , 2).Offset(1)

  5. #5
    Quote Originally Posted by mana View Post
    Sheet1.Cells(1).CurrentRegion.Offset(1).Copy _
        Sheet3.Cells(1).ListObject.Range.Columns(2).Find("*", , , , , 2).Offset(1)
    Sheet2.Cells(1).CurrentRegion.Offset(1).Copy _
        Sheet3.Cells(1).ListObject.Range.Columns(2).Find("*", , , , , 2).Offset(1)
    thanks so much the code is perfect but i have a problem when i press this macro it repeats the data more than time i ment when i add a new data and press macro it repeats the old data which added earlier

  6. #6
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Sub test()
        Dim tbl As ListObject
        
        Set tbl = Sheet3.Cells(1).ListObject
        
        tbl.DataBodyRange.Columns(2).Resize(, 3).ClearContents
    
        Sheet1.Cells(1).CurrentRegion.Offset(1).Copy _
                tbl.Range.Columns(2).Find("*", , , , , 2).Offset(1)
        Sheet2.Cells(1).CurrentRegion.Offset(1).Copy _
                tbl.Range.Columns(2).Find("*", , , , , 2).Offset(1)
    
    End Sub

  7. #7
    Quote Originally Posted by mana View Post
    Sub test()
        Dim tbl As ListObject
        
        Set tbl = Sheet3.Cells(1).ListObject
        
        tbl.DataBodyRange.Columns(2).Resize(, 3).ClearContents
    
        Sheet1.Cells(1).CurrentRegion.Offset(1).Copy _
                tbl.Range.Columns(2).Find("*", , , , , 2).Offset(1)
        Sheet2.Cells(1).CurrentRegion.Offset(1).Copy _
                tbl.Range.Columns(2).Find("*", , , , , 2).Offset(1)
    
    End Sub

    it gives me error
    error2.JPG

  8. #8
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Set tbl = Sheet3.ListObjects(1)

  9. #9
    Quote Originally Posted by mana View Post
    Set tbl = Sheet3.ListObjects(1)
    i changed but it gives me the same error

  10. #10
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    sorry

  11. #11
    what you mean it's done in this point

Posting Permissions

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