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]

Try this macro

Sub Insert_data()
  Dim sh1 As Worksheet, sh2 As Worksheet, lr1 As Long, lr2 As Long, wmax As Long
  Set sh1 = Sheets("Aste")
  Set sh2 = Sheets("Datio")
  lr1 = sh1.Range("C" & Rows.Count).End(xlUp).Row
  lr2 = sh2.Range("C" & Rows.Count).End(xlUp).Row - 1
  wmax = sh1.Range("C" & lr1).Value + 1
  sh1.Range("A" & lr1 + 1).Resize(lr2, 9).Value = sh2.Range("A2").Resize(lr2, 9).Value
  sh1.Range("C" & lr1 + 1).Resize(lr2).Value = wmax
End Sub

 

[richard1234567891011]

Thank you very much.

But The macro stops to work here: wmax = sh1.Range("C" & lr1).Value + 1
If this part is related to asset column and the asset counting, I already have a formula in C5 (table expected results), because data starts in C5 in both sheets.

=IF(E5="";"";IF(A5=1;1;IF(E5=E4;SUM(C4);SUM(C4)+1)))

Therefore I can also paste staring from the column D.

Thank you.

 

[DanteAmor]

That was not in your original requirement.
What the macro does is continue with the numbering, just as you put it in your examples.
Delete your formulas and try the macro again.

 

[richard1234567891011]

That was not in your original requirement.
What the macro does is continue with the numbering, just as you put it in your examples.
Delete your formulas and try the macro again.

Thank you very much, but is it possibile to change it a little?

It is perfect, but I would like this 2 changes:

Cell reference no A1 but A4 as first, so, in A5 the first 1
The merge of the two tables not in the same table, but in another one, called for example master or something similar

I don't have experience in macros so I don't know how to make this 2 changes.

Thank you very much, you are helping me a lot.

 

[DanteAmor]

I'm sorry but I didn't understand any of the 2 changes.
You can give several examples of what you have and what you expect from the result.

Upload an excel range:
XL2BB - Excel Range to BBCode

 

[richard1234567891011]

I'm sorry but I didn't understand any of the 2 changes.
You can give several examples of what you have and what you expect from the result.

Upload an excel range:
XL2BB - Excel Range to BBCode

Cell reference no A1 but A4 as first, so, in A5 the first 1
The merge of the two tables not in the same table, but in another one, called for example master or something similar

In This way, as the screen.

 

[DanteAmor]

I updated the macro so you could keep your formula in the "Aste" sheet column C sheet

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 = 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 + 1
  sh3.Range("A" & lr3 + 1).Resize(lr2, 9).Value = sh2.Range("A2").Resize(lr2, 9).Value
  sh3.Range("C" & lr3 + 1).Resize(lr2).Value = wMax
End Sub

 

[richard1234567891011]

I updated the macro so you could keep your formula in the "Aste" sheet column C sheet

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 = 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 + 1
  sh3.Range("A" & lr3 + 1).Resize(lr2, 9).Value = sh2.Range("A2").Resize(lr2, 9).Value
  sh3.Range("C" & lr3 + 1).Resize(lr2).Value = wMax
End Sub

Thank you very much,

But there is something wrong, or I am doing something wrong.

Before It pasted two times the sheet Datio in the sample sheet in the screen,
Now is not working.

In the sample Sheet (without formula the one in the screen) I have this error: sh3.Range("A" & lr3 + 1).Resize(lr2, 9).Value = sh2.Range("A2").Resize(lr2, 9).Value

In the original sheet instead (with formula, the one were I have to really insert the macro, you didn't see it) I have this error: wMax = sh3.Range("C" & lr3).Value + 1

I am not forced to use a macro that allow me to have formulas in some cells, I only need a macro that built what is in the screen.

Thank you very much, you are very kind.

 

[richard1234567891011]

If you want, this is the file

prova macro.xlsm

Shared with Dropbox

www.dropbox.com