VBA copy data from one table to another with loop

[Koxeida]

Hi guys,

I have the following situation.

Table 1 in Sheet ("PH")

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Person A
[/TD]
[TD]Country A
[/TD]
[TD]Country B
[/TD]
[TD]Country C
[/TD]
[TD]Country D
[/TD]
[TD]Country E
[/TD]
[/TR]
[TR]
[TD]Product A
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]Product B
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]25
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]Product C
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]Product D
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]17
[/TD]
[TD="align: center"]25
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]Product E
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
</tbody>[/TABLE]

Table 2 in Sheet ("Compiled")

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Person
[/TD]
[TD]Country
[/TD]
[TD]Product
[/TD]
[TD]Description (some formula)
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]Person A
[/TD]
[TD="align: center"]Country A
[/TD]
[TD="align: center"]Product A
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5
[/TD]
[/TR]
[TR]
[TD]Person A
[/TD]
[TD="align: center"]Country A
[/TD]
[TD="align: center"]Product B
[/TD]
[TD="align: center"]<strike></strike>
[/TD]
[TD="align: center"]5
[/TD]
[/TR]
[TR]
[TD]Person A
[/TD]
[TD="align: center"]Country A
[/TD]
[TD="align: center"]Product C
[/TD]
[TD="align: center"]<strike></strike>
[/TD]
[TD="align: center"]5
[/TD]
[/TR]
[TR]
[TD]Person A
[/TD]
[TD="align: center"]Country A
[/TD]
[TD="align: center"]Product D
[/TD]
[TD="align: center"]<strike></strike>
[/TD]
[TD="align: center"]5
[/TD]
[/TR]
[TR]
[TD]Person A
[/TD]
[TD="align: center"]Country A
[/TD]
[TD="align: center"]Product E
[/TD]
[TD="align: center"]<strike></strike>
[/TD]
[TD="align: center"]5
[/TD]
[/TR]
[TR]
[TD]Person A
[/TD]
[TD="align: center"]Country B
[/TD]
[TD="align: center"]Product A<strike></strike>
[/TD]
[TD="align: center"]<strike></strike>
[/TD]
[TD="align: center"]10
[/TD]
[/TR]
</tbody>[/TABLE]


So I want to copy my data from Table 1 to Table 2 as shown above. However, before doing this process, I also wanna clear the existing data in the table 2 (Delete table rows). So far this is the code that I have

Dim wsComp As Worksheet
Set wsComp = Sheets("Compiled")
Set wsPH = Sheets("PH")

Dim lrow As Long
On Error Resume Next
    lrow = wsComp.Range("A" & Rows.Count).End(xlUp).Row   
    
    If lrow > 1 Then
    
    wsComp.Range("A2:C" & lrow).ClearContents
    wsComp.Range("E" & lrow).ClearContents
      
  [COLOR=#ff0000](Clear contents is not what I'm looking for. But I couldn't think of a substitute code that would only delete the table rows and not the entire row)[/COLOR]
    
    End If
    
Dim i, j As Long
    For i = 2 To wsPH.Cells(Rows.Count, 1).End(xlUp).Row
        j = wsComp.Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        wsComp.Cells(j, "A").Value = wsPH.Range("A1").Value


    Next

So I was just testing out one column value first and I've already encountered an issue. After clearing the data in the table, the code begins to copy data from Table 1 to outside (below the last row) of Table 2.

I also have no idea how I can loop the code to work it the way I want it to be.

Any help would be great. Thank you!!

 

[My Aswer Is This]

We really do not need to use a loop when filtering a table.

So what column in Table1 do you want to filter on
And what is the filter value

For example say something like this.

On Sheet PH filter column 4 of Table 1 using the filter value Me

And filter to sheet

Table 2 in Sheet ("Compiled")​

 

[Koxeida]

The crux of the problem is that I want to copy the data from Table 1 to Table 2, row by row and column by column

I sort of figured out the row by row part. But I'm not sure how I can combine that with column by column copying. Hence, I thought I would require looping. Is there other way I could've done?

 

[My Aswer Is This]

You answered none of my questions:
Here they are again:

So what column in Table1 do you want to filter on
And what is the filter value

For example say something like this.

On Sheet PH filter column 4 of Table 1 using the filter value Me

And filter to sheet

Table 2 in Sheet ("Compiled")​

 

[Koxeida]

So basically I want to copy

- A1 in Sheet ("PH") to column A in Sheet ("Comp")
- B1: (end of last column) in Sheet ("PH") to column B in Sheet ("Comp")
- A2: (end of last row) in Sheet ("PH") to column C in Sheet ("Comp")
- B2: (end of last row and column) in Sheet ("PH") to column E in Sheet ("Comp")

So I want it to do it row by row on the basis of
- Product followed by country (with their corresponding value) as shown in the example table 2

Thanks!