copy to another worksheet based on criteria

[m09151014]

2 days in a row I have had to ask a question. I have spent most of the day looking thru already answered questions but, I can't find exactly what I need.

THANKS in advance for the help!

I have 2 worksheets one has list of store numbers in column A column N has a reason code for every store listed in column A. I need to bring the store number to a separate sheet based on the reason of County Ordered (or whatever word it might be) I need the numbers in my second work sheet to be on consecutive rows and not skip rows.

I have a screen shot that might help if my question is a little crazy.

This is the original report if the reason in Column N is "County Ordered" I need the store number in Column A to populate on a second sheet. Notice there are lots of Hurricane Isaias but, I need that first row on the new sheet to be the store number for "County Ordered" (row 19) on the first row in my spreadsheet and not skip rows with store numbers that do not meet my criteria.

 

[rpaulson]

try this on a copy of your file

Sub Create_Worksheets()
'This macro will seperate the data in  your range into individual worksheet(s)
'it will look in Column of every row in your data
'it will then add that row of data into a worksheet whose sheet name is the value in columnn
'if the worksheet does not exist, the code will automatically create and name the new sheet for you.

Dim rs As Worksheet
Set rs = ActiveSheet

For r = 6 To rs.Range("N" & Rows.Count).End(xlUp).Row
wsName = rs.Cells(r, "N") '<<<<<<<<<<<<<<<<<<<<<  Change this to the column that you would like to seperate
If wsName = "" Then GoTo 10

If WorksheetFunction.IsErr(Evaluate("'" & wsName & "'!A1")) = "True" Then Sheets.Add.Name = wsName     'if true then sheet does NOT exist, if False then sheet does exist

wr = Worksheets(wsName).Range("A" & Rows.Count).End(xlUp).Row + 1
rs.Rows(r).Copy Destination:=Worksheets(wsName).Range("A" & wr)

10 Next r
rs.Activate
MsgBox "Done"

End Sub

hth,

Ross

 

[m09151014]

I appreciate your help I probably should have said in my original post no VBA. I don't understand it I was looking for maybe an array formula. Again sorry for the trouble!

 

[Fluff]

How about
=IFERROR(INDEX(Sheet1!$A$6:$A$100,AGGREGATE(15,6,(ROW(Sheet1!$A$6:$A$100)-ROW(Sheet1!$A$62)+1)/(Sheet1!$N$2:$N$100="County Ordered"),ROWS(A$2:A2))),"")
Change sheet name to suit & change part in red to match the cell you put the formula into

 

[m09151014]

How about
=IFERROR(INDEX(Sheet1!$A$6:$A$100,AGGREGATE(15,6,(ROW(Sheet1!$A$6:$A$100)-ROW(Sheet1!$A$62)+1)/(Sheet1!$N$2:$N$100="County Ordered"),ROWS(A$2:A2))),"")
Change sheet name to suit & change part in red to match the cell you put the formula into

THANKS so much but, I can't get it to work. I didn't get an error but, I didn't get a store number from the other sheet either just blanks. I actually changed my Sheet name to Sheet1 so I knew I would mess that up, but no luck.

 

[Fluff]

In that case can you please use the XL2BB add-in to post a sample of your data for sheets.