Paste data in Excel Table

[Mo7a86]

Hi

I want to copy data (value only) from another sheet to my table with keeping my table format (Using office 2013)

copy the below data

Book1
	B	C	D	E
2	Column2	Column1	Column3	Column4
3	1	a	a1	1/1/2022
4	2	b	a2	1/2/2022
5	3	c	a3	1/3/2022
6	4	a	a4	1/4/2022
7	5	b	a5	1/5/2022
8	6	c	a6	1/6/2022
9	7	a	a7	1/7/2022
10	8	b	a8	1/8/2022
11	9	c	a9	1/9/2022
12	10	a	a10	1/10/2022
13	11	b	a11	1/11/2022
14	12	c	a12	1/12/2022
15	13	a	a13	1/13/2022
16	14	b	a14	1/14/2022
17	15	c	a15	1/15/2022
18	16	a	a16	1/16/2022
19	17	b	a17	1/17/2022
Sheet2

to this below table sheet

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		table 1
2		Column1	Column2	Column3	Column4
3		QAR 1.00	s	a	1-Jan-2022
4		QAR 2.00	g	y	2-Jan-2022
5		QAR 2.00	h	t	3-Jan-2022
6		QAR 5.00
7
8
9
10		table 2
11		Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11
12		QAR 1.00	a	d	r	e	e	d	d	j	c	5-May-2022
13		QAR 1.00	b	e	f	r	e	s	s	k	r	6-May-2022
14		QAR 1.00	c	f	g	f	w	w	s	g	t	7-May-2022
15		QAR 3.00
Sheet1

Cell Formulas
Range	Formula
B6,B15	B6	=SUBTOTAL(109,[Column1])

i want to paste values only in the first table to match destination formatting
hint: the second sheet has many tables which make it not possible to just copy and paste

 

[Mo7a86]

Is it too easy or no solution ?

 

[Mo7a86]

??

 

[Alex Blakenburg]

hint: the second sheet has many tables which make it not possible to just copy and paste

That was a good call out

Give the below a try:
Note: it probably won't work if you have tables "next to" the table you are inserting rows into, since it is inserting entirerows
Also I didn't have your table names so you will need to insert the Copy FROM and Copy TO table names.

Sub CopyTableData()

    Dim srcTbl As ListObject, destTbl As ListObject
    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcRowCnt As Long, destRowLast As Long
    
    Set srcTbl = Range("tblSource").ListObject      ' <----- Put in correct table name for Copy FROM Table
    Set destTbl = Range("Table1").ListObject        ' <----- Put in correct table name for Copy TO Table
    Set destSht = destTbl.Parent

    srcRowCnt = srcTbl.DataBodyRange.Rows.Count
    destRowLast = destTbl.DataBodyRange.Rows(destTbl.DataBodyRange.Rows.Count).Row
    
    ' Insert rows below table to make room for table expansion
    destSht.Rows(destRowLast + 2).Resize(srcRowCnt).Insert
    ' Insert rows inside table
    destSht.Rows(destRowLast + 1).Resize(srcRowCnt).Insert
    
    srcTbl.DataBodyRange.Copy
    destSht.Cells(destRowLast + 1, destTbl.Range.Cells(1, 1).Column).PasteSpecial Paste:=xlPasteValues
    
    ' Get new last row
    destRowLast = destTbl.DataBodyRange.Rows(destTbl.DataBodyRange.Rows.Count).Row
    ' Delete rows inserted below table
    destSht.Rows(destRowLast + 2).Resize(srcRowCnt).Delete
    
End Sub

 

[Mo7a86]

Hi alex thanks for the reply no the tables are above each other but it is like 10 tables i should do this vba and change the names each

So each time i have to write new tables name ?

 

[Alex Blakenburg]

I am obviously not fully understanding what you are trying to do.
At the moment your source format lines up in format to Table 1 on sheet 2 so I am copying the rows there.
Please give details and examples of what else you want it to do.

 

[Mo7a86]

Well to be more accurate i am working in a bank so i do some financial analysis the form i have now is word documents so i thought it might be easier if i use excel because the statements i can generate it as excel file too

So the point of my question if i have in the statement like 1000 debit chqs (which means 1000 row)

I want to paste it in my second sheet table 1 with matching the table 1 format not the statement format also shifting the below table which is table 2 down and not writing above it ... what i am doing now is inserting copied cells (i talk the whole row so the below table fully shifted down) between the existing rows so the formulas adjusted automatically and then format painting after that i remvoe the empty cells which i use it in the format painting so i want to know if i can merge all these steps in one formula or vba or any other solution

In my current situation the tables option not working for me so i am working it as normal range but as i said before i have to do three steps each time and in tables i cant insert rows when i have more than one table above each other

So i want a 1 step solution just copy paste and have th shifting down and maych destination formatting and formula adjusted automatically it is sum formula btw

Hope it is more clear now

 

[Alex Blakenburg]

Did you try the macro ?
If so show me what it did and then what you wanted it to do so I can see the difference.

Is is just that you want column 1 & 2 swapped ?