Insert data from different sheets into one

[richard1234567891011]

Hello Guys,

I have two similar tables in different sheets and I want them both in one sheet.

What do you think is the best way?

A macro? A formula?
And in case, what is the macro or formula?

This is the example:
Sheet: Aste

Sheet: Datio

Expected result:
Sheet: Aste+Datio

Thank you guys,
Kind regards.

 

[DanteAmor]

I am not understanding what you have on each sheet.
You are supposed to have this on each sheet.

Book1
	A	B	C	D	E	F	G	H	I
1	num	cda	asset	Datio	ct	sub	tip	ndg	mq
2	1	11/11/2019	1	aste2	ct2	sub2	tip2	ndg2	mq2
3	2	12/11/2019	2	aste3	ct3	sub3	tip3	ndg3	mq3
4	3	13/11/2019	3	aste4	ct4	sub4	tip4	ndg4	mq4
5	4	14/11/2019	4	aste5	ct5	sub5	tip5	ndg5	mq5
6	5	15/11/2019	5	aste6	ct6	sub6	tip6	ndg6	mq6
Aste

Book1
	A	B	C	D	E	F	G	H	I
1	num	cda	asset	aste	ct	sub	tip	ndg	mq
2	1	11/11/2019	1	aste2	ct2	sub2	tip2	ndg2	mq2
3	2	12/11/2019	1	aste3	ct3	sub3	tip3	ndg3	mq3
4	3	13/11/2019	1	aste4	ct4	sub4	tip4	ndg4	mq4
5	4	14/11/2019	1	aste5	ct5	sub5	tip5	ndg5	mq5
6	5	15/11/2019	1	aste6	ct6	sub6	tip6	ndg6	mq6
7	6	16/11/2019	1	aste7	ct7	sub7	tip7	ndg7	mq7
8	7	17/11/2019	1	aste8	ct8	sub8	tip8	ndg8	mq8
9	8	18/11/2019	1	aste9	ct9	sub9	tip9	ndg9	mq9
10	9	19/11/2019	1	aste10	ct10	sub10	tip10	ndg10	mq10
11	10	20/11/2019	1	aste11	ct11	sub11	tip11	ndg11	mq11
Datio

For that the macro works.

If you have something else on your sheets and the macro sends an error, then you must tell me what you have on each sheet, the macro's error message and the line where the code stops.

 

[DanteAmor]

I needed to update the macro to start in row 5 on each sheet.
Try this:

Sub Insert_data()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim lr1 As Long, lr2 As Long, lr3 As Long, wMax As Long
  Set sh1 = Sheets("Aste")
  Set sh2 = Sheets("Datio")
  Set sh3 = Sheets("Master")
  sh3.Rows("5:" & Rows.Count).ClearContents
  lr1 = 5
  Do While sh1.Range("C" & lr1).Value <> ""
    lr1 = lr1 + 1
  Loop
  lr1 = lr1 - 1
  lr2 = 5
  Do While sh2.Range("C" & lr2).Value <> ""
    lr2 = lr2 + 1
  Loop
  lr2 = lr2 - 5
  sh3.Range("A5").Resize(lr1 - 4, 9).Value = sh1.Range("A5").Resize(lr1 - 4, 9).Value
  lr3 = sh3.Range("C" & Rows.Count).End(xlUp).Row
  wMax = sh3.Range("C" & lr3).Value + 1
  sh3.Range("A" & lr3 + 1).Resize(lr2, 9).Value = sh2.Range("A5").Resize(lr2, 9).Value
  sh3.Range("C" & lr3 + 1).Resize(lr2).Value = wMax
End Sub

 

[richard1234567891011]

Yes!

This is so good.

Perfect!!!!!!

ahaha!!!

Thank you soooo much!!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[richard1234567891011]

I'm glad to help you. Thanks for the feedback.

Dear DanteAmor,

Thank you again so much, I am using your macro and it is really really cool,

One last thing, The macro actually works and see until the column I. So It is able to read 9 Columns.

Is it possibile to increase the column to read?
So until column AS, so 45 columns.
Thank you again.

 

[DanteAmor]

Dear DanteAmor,
One last thing, The macro actually works and see until the column I. So It is able to read 9 Columns.
Is it possibile to increase the column to read?
So until column AS, so 45 columns.

Change the number 9 in the macro to the number 45, try and tell me.

 

[richard1234567891011]

Yes it works! thank you so much.

This macro is too cool.
Thank you!

 

[DanteAmor]

Again with pleasure. Thanks for the feedback

 

[richard1234567891011]

Again with pleasure. Thanks for the feedback

Dear DanteAmor,
I saw now an issue in your macro. It is indeed my fault because the macro is correct based on the data that I gave you.

The issue is that in the second file, it is not able to continue the counting.

If I add this line. Mantova. It is not able intact to increase the number.
The result is:

But it is wrong, because the 6 of Mantova should be 7.
It is not continuing the same counting as the first part.

Thank you again,
You are very kind.

 

[DanteAmor]

Try this

Sub Insert_data()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim lr1 As Long, lr2 As Long, lr3 As Long, wMax As Long, i As Long
 
  Set sh1 = Sheets("Aste")
  Set sh2 = Sheets("Datio")
  Set sh3 = Sheets("Master")
 
  sh3.Rows("5:" & Rows.Count).ClearContents
  lr1 = 2
  Do While sh1.Range("C" & lr1).Value <> ""
    lr1 = lr1 + 1
  Loop
  lr1 = lr1 - 1
  lr2 = 2
  Do While sh2.Range("C" & lr2).Value <> ""
    lr2 = lr2 + 1
  Loop
  lr2 = lr2 - 2
  sh3.Range("A5").Resize(lr1, 9).Value = sh1.Range("A2").Resize(lr1, 9).Value
  lr3 = sh3.Range("C" & Rows.Count).End(xlUp).Row
  wMax = sh3.Range("C" & lr3).Value
  sh3.Range("A" & lr3 + 1).Resize(lr2, 9).Value = sh2.Range("A2").Resize(lr2, 9).Value
  For i = lr3 + 1 To sh3.Range("C" & Rows.Count).End(xlUp).Row
    sh3.Range("C" & i).Value = sh3.Range("C" & i).Value + wMax
  Next
End Sub