CODEWITHSUNDEEP
vbaexcelformatcopy
Catarina Ribeiro
Catarina Ribeiro

Reputation: 646

Copy filled rows from table to another

I am new to VBA and I'm trying to write a Macro in Excel that copies a table to another table and then deletes lines where a cell is equal to the value "0".

I have a table as follows:

enter image description here

I want the table to look like this:

enter image description here

Can anyone help me?

Upvotes: 0

Views: 118

Answers (1)

CLR
CLR

Reputation: 12279

The following will copy data from a table in Sheet1 to Sheet2 where the second column is not 0 - using the table's Autofilter.

I had to add a 1 second application.wait to allow the filter to be applied before copying. This might need increasing depending on table size. Perhaps someone else could help with a more robust method of waiting for the filter to apply..?

Dim source_sheet As Worksheet
Dim destination_sheet As Worksheet

Set source_sheet = Sheets("Sheet1")
Set destination_sheet = Sheets("Sheet2")

    With source_sheet.ListObjects("Table1").Range
        .AutoFilter Field:=2, Criteria1:="<>0"
        Application.Wait 1
        .Copy Destination:=destination_sheet.Range("A1")
        .AutoFilter Field:=2
    End With

It's worth mentioning that the output to Sheet2 is not in a true table form. However, it shouldn't be tricky to record a macro where you convert it to a table to gain that piece of code too.

Upvotes: 1

Related Questions