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!


[TABLE="width: 500"]
<tbody>[TR]
[TD]Comp[/TD]
[TD]ID[/TD]
[TD]Wt%[/TD]
[TD][/TD]
[TD]Comp[/TD]
[TD]ID[/TD]
[TD]wt%[/TD]
[/TR]
[TR]
[TD]x1[/TD]
[TD]aa[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]x2[/TD]
[TD]bb[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]x2[/TD]
[TD]bb[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]x5[/TD]
[TD]ee[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]x3[/TD]
[TD]cc[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]x6[/TD]
[TD]ff[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]x4[/TD]
[TD]dd[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x5[/TD]
[TD]ee[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x6[/TD]
[TD]ff[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.