Copy excel row from one worksheet to another based on cell contents

[samanthanyw]

I'm fairly new to VBA and thus I need some help with this. I've looked online countless times but I can never seem to find exactly what i'm looking for (or it doesn't work at all).

I have an excel workbook with two tabs - Activities and Travel. Whenever a number (other than 0) is entered into the column "# of Travel Shifts" on the Activities tab, I've like that whole row to be copied onto the Travel tab.

I've attached a picture so that you know exactly what i'm referring to.

My second issue is that this workbook will be constantly updated - and I do not want to repeat the rows on the Travel tab - but instead update the travel tab. So for example, if I put travel days at 2 for row 8 - then that row would show up on the travel tab. But if I change that 2 to a 0 - then that row will be removed from the Travel tab.

Does that make sense?
I'm not sure if VBA is the way to do any of this - but I was hoping it might be a good place to start.

 

[stunnrock]

This can be done without VBA, if you're happy to add one helper column.
On your Travel sheet, insert a new column before the table, then:
In Travel!A6 =ROW(Activities!$A$7)-1
In Travel!A7 =A6+MATCH(TRUE,INDEX(INDEX(Activities!$I:$I,A6+1):Activities!$I$30>0,),0)
($30 must refer to a row below your Activities table)
Copy A7 down as far as required.
In Travel!B7 =IF(ISNA($A7),"",INDEX(Activities!A:A,$A7))
Copy B7 along and down the entire table in the Travel sheet.

Job done.

 

[samanthanyw]

AMAZING!!
Thank you SO MUCH!