PDA

View Full Version : help adjusting code combine two tables from two sheets to another sheet



maghari
10-13-2019, 11:20 PM
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

Sub CopyTables()
sheet1.ListObjects("Table1").Range.Copy _
Destination:=sheet3.Range("B1")

sheet1.ListObjects("Table2").Range.Copy _
Destination:=sheet3.Range("B4")
End Sub

snb
10-13-2019, 11:49 PM
Read a book on VBA for beginners.
Follow a course for VBA-beginners.

maghari
10-14-2019, 01:41 AM
Read a book on VBA for beginners.
Follow a course for VBA-beginners.
i just need adjusting this code is there any way

mana
10-14-2019, 04:42 AM
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)

maghari
10-14-2019, 05:37 AM
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

mana
10-14-2019, 06:16 AM
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

maghari
10-14-2019, 06:33 AM
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
25278

mana
10-14-2019, 06:49 AM
Set tbl = Sheet3.ListObjects(1)

maghari
10-14-2019, 07:08 AM
Set tbl = Sheet3.ListObjects(1)
i changed but it gives me the same error

mana
10-14-2019, 07:17 AM
sorry:bow:

maghari
10-14-2019, 07:35 AM
what you mean it's done in this point