Creating a second table based off values on the last column of the original table

[vbaturk]

I'm trying to create a second table from an original table based off of non zero values indicated in the third column of the original table. I need to do this using vba / macro, here is a visual of what I need to do.

the range of the table is cells G10 to I47, this is just a snippet. I was thinking of looping through the range and if last column value isn't zero copy to new range. But not sure how to do it... Thank you!

Comp	ID	Wt%		Comp	ID	wt%
x1	aa	0		x2	bb	2
x2	bb	2		x5	ee	3
x3	cc	0		x6	ff	4
x4	dd	0
x5	ee	3
x6	ff	4

<tbody>
</tbody>

 

[alansidman]

Here is one option

Option Explicit


Sub NewTbl()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    s1.Range("G1:I1").Copy s2.Range("A1")
    Dim i As Long
    Dim lr As Long, last As Long
    lr = s1.Range("G" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        last = s2.Range("A" & Rows.Count).End(xlUp).Row
        If s1.Range("I" & i) <> 0 Then
            s1.Range("G" & i & ":I" & i).Copy s2.Range("A" & last + 1)
        End If
    Next i
End Sub

Alternatively, you could filter on Column I and then copy and paste the results.

Option Explicit


Sub Macro1()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim lr As Long
    lr = s1.Range("G" & Rows.Count).End(xlUp).Row
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    s1.Range("G1:I1").AutoFilter
    s1.Range("G1:I" & lr).AutoFilter Field:=3, Criteria1:=Array("2", _
        "3", "4"), Operator:=xlFilterValues
    lr = s1.Range("G" & Rows.Count).End(xlUp).Row
    s1.Range("G1:I" & lr).Copy s2.Range("A1")
    Sheets("Sheet2").Select
End Sub

 

[vbaturk]

Worked like a charm! Thank you so much!

 

[vbaturk]

so I'm adding another level of complexity
I want to create multiple tables next to the new table, but still by just hitting the macro button. Essentially I would update the values in the original table again, hit my macro button and a second new table would be populated next to the first new table. I want to be able to do this multiple times.

Would I essentially just create a counter and offset + 4 on every column variable within the code? since I know the width of every table is 3?
Not sure about the best way of going about doing this.