VBA to copy data from 2 tables on Sheet1 to one table on Sheet2

[JARichard74]

I want to copy the data from Table 1 and 2 in Image1 to Table3 so that it looks like Image2. The number of items in Tables 1 and 2 can vary. I have figured out how to add the correct number of rows to table 3 but cannot figure how to copy the data.

 

[rpaulson]

try this on a copy of your worksheet

Sub Do_it()

Dim ws As Worksheet
Set ws = Worksheets("Sheet2") 'adjust the name as needed.

wr = 7

For r1 = 12 To 19
ws.Cells(wr, "A") = Cells(r1, "F")
ws.Cells(wr, "C") = Cells(r1, "H")
wr = wr + 1

For r2 = 21 To 24
ws.Cells(wr, "A") = Cells(r2, "A")
wr = wr + 1

Next r2
Next r1
End Sub

hth,
Ross

 

[JARichard74]

I will test this weekend however at first glance I believe it will only work as it appears in image 1. As mentioned in my post, the number of rows in Tables 1 and 2 will vary. So r1 won't always be 12 to 19 and r2 won't always be 21 to 24. r1 will always start at 12 but would end at lastrow. r2 is more variable because the table above could vary as well so r2 could be anywhere from 19 to 30 or 40+.

 

[rpaulson]

change
For r1 = 12 To 19
to
For r1=12 to Cells(Rows.Count, "F12").End(xlDown).Row

 

[JARichard74]

For r1=12 to Cells(Rows.Count, "F12").End(xlDown).Row

Get a few errors. First one, wr variable not defined so I Dim wr As Integer. Then r1 and r2 variables are not defined so did the same. The I get Application-defined or object-defined error. I must clarify that Tables 1 and 2 in Image one are in Worksheets("Prep") and Table 3 in Image 2 is in Worksheets("Evaluator")

 

[rpaulson]

change
Set ws = Worksheets("Sheet2") 'adjust the name as needed.
to
Set ws = Worksheets("Evaluator") 'adjust the name as needed.

 

[JARichard74]

Get the error Application-defined or object-defined error on this line
For r1 = 12 To Cells(Rows.Count, "F12").End(xlDown).Row

 

[rpaulson]

oops.
For r1 = 12 To Range("F12").End(xlDown).Row

 

[JARichard74]

Thanks. Made a couple of corrections as it was not copying the correct columns. However, Table 2 won't always start at row 21. In picture 1, the table Mandatory Criteria might have only one row which makes table 2 move up. What you provided is a solution to what was presented so I will mark it as solution.