VBA Loop & Offset Syntax

I need a vba command that searches column J for a text value, when the value is identified the row is selected and cut and pasted into_
a new worksheet. A new worksheet should be created for every unique value of column J. Second part: Lets say column say had
5 unique values so there are 5 new worksheets. Each worksheet contains 30-50 rows (and 10 columns) for example, I need to sort the rows based on 3 criteria
this is easily accomplished by the Sort dialogue box however it creates static references in the vba code (i.e., A2:A50) however
the references need to be dynamic so that once a new set of data is worked on with this macro, all the rows (>50 if needed) could
be analyzed. I've tried using the offset function to address this last problem but I cannot seem to get the synthax down.

searches column J for a text value

does that mean some values in column J are not text . what are they . I presume they are not blanks but logical values or numbers.

I also do not know what is the configuration of your data. I have created a sample configuration. I have sent the whole workbook and you can download from this webpage(as it may perhaps be large for parking in this newsgroup). you see the data in sheet1

http://www.speedyshare.com/files/234...bgintheusa.xls

the macro "test" is in the vb editor module. the second macro "undo" will remove the results of the macro "test". at present the workbooks, as it is, after running the macro "test"
for rechecking
1. run "undo"
2. run "test"

the macros are also given below for completions' sake

Please Login or Register  to view this content.

Please Login or Register  to view this content.

YOUR COMMENTS PLEASE.