Help Please - Excel Macro/VBA ?

[The Brown Growler]

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 "Order Status" and contains text values such as "On Hold", "Delivered". 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 "Order Status" column is equal to "On Hold". 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
Growlos

 

[Brianwarnock]

I assume the sheet that you want to copy the data to is the active sheet, therefore use Activesheet.index to find its index number and then subtract 1 to find the previous sheet index, then you can use this info in your code to address the cells.

Brian

 

[The Brown Growler]

I assume the sheet that you want to copy the data to is the active sheet, therefore use Activesheet.index to find its index number and then subtract 1 to find the previous sheet index, then you can use this info in your code to address the cells.

Brian

Brian,

Thank you for the reply with the tip about activesheet etc.
After a quick "Google" I have managed to get something almost working that I can build on.

ManyThx
Growlos

 

[Brianwarnock]

Hi
Glad to hear you are making progress. Rereading your post this morning I realised that you had an "index" in your sheetname, thus you might prefer the code below , in which case the order of the sheets would not matter.

Dim ws As Worksheet, x As String

x = ActiveSheet.Name
Set ws = Sheets("week" & Mid(x, 5) - 1)

'then code of this form but indexing on rownum
ActiveSheet.Cells(1, 1) = ws.Cells(1, 1)

Brian