Excel Help Required With Macro/VBA

Would any of the excel gurus please be able to help me with a macro to run in an excel 2010 workbook?

I wish to create a macro that checks the worksheet to the immediate left of the worksheet with the macro in it and looks for a text string in the cells of a column. When it finds the precise text string, it copies the row containing the text string to the worksheet containing the macro. There will be more than one row to be copied, it needs to copy all relevant rows. In the worksheet with the macro that receives the copied rows, the copy should start at row 30

For example, sheet named "Week45" contains 5 columns and 200 rows with data. One of the columns is labelled "Status" and contains text values such as "Follow", "Discard". The sheet to the immediate right is named "Week46" and it contains the macro. When the macro is activated it checks "week45" and copies all rows to "Sheet46" where the text value in the "Status" column is equal to "Follow". The copied data is placed in row 30 onwards in the sheet "Week46"

I will be creating new sheets as the weeks evolve so need the sheet names not to be hard coded in the macro, ie, not containing the tab name but some other method to reference the sheet containing the data to be copied. I could set it all up manually by creating 52 sheets and setting up lots of macros by recording keystrokes and using "Auto Filter" to select the rows, however, it is a bit clunky and not very flexible. I hope the details are clear enough, any help most appreciated.

Thx