Solved: SEARCH COLUMN FOR VALUE AND COPY ROW

[pdeshazier]

i have a spreadsheet that for every row with "DC" in column L, i need the entire row copied to a new spreadsheet. file attached.

[p45cal]

Make sure that sheet 'SUR DC PATIENTS LIST' has no autofilter, then run this:[vba]Sub blah()
With Sheets("SUR DC PATIENTS LIST")
.Rows("1:1").Insert
.Range("A1") = "blah"
.UsedRange.AutoFilter Field:=12, Criteria1:="DC"
.UsedRange.SpecialCells(xlCellTypeVisible).Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
ActiveSheet.Rows("1:1").Delete
.UsedRange.AutoFilter 'removes autofilter
.Rows("1:1").Delete
End With
End Sub
[/vba] If you need it, I can attach a file.

[pdeshazier]

thanks. i was waiting for an email and just checked this. thanks for your help!! i do appreciate it.. i am about to try it.

Make sure that sheet 'SUR DC PATIENTS LIST' has no autofilter, then run this:[vba]Sub blah()
With Sheets("SUR DC PATIENTS LIST")
.Rows("1:1").Insert
.Range("A1") = "blah"
.UsedRange.AutoFilter Field:=12, Criteria1:="DC"
.UsedRange.SpecialCells(xlCellTypeVisible).Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
ActiveSheet.Rows("1:1").Delete
.UsedRange.AutoFilter 'removes autofilter
.Rows("1:1").Delete
End With
End Sub
[/vba] If you need it, I can attach a file.

[pdeshazier]

thanks so much!!!!

[pdeshazier]

one more question: right now the code places the results on one spreadsheet (sheet2). is there a way to copy to separate sheets based on value in column J? for instance, all the DC rows for col J = HOSPICE - INPATIENT GENERAL need to be on a spreadsheet labeled that value

[p45cal]

one more question: right now the code places the results on one spreadsheet (sheet2). is there a way to copy to separate sheets based on value in column J? for instance, all the DC rows for col J = HOSPICE - INPATIENT GENERAL need to be on a spreadsheet labeled that value

[vba]Sub blah()
Dim Dict As Object
Set Dict = CreateObject("Scripting.Dictionary")
With Sheets("SUR DC PATIENTS LIST")
.Rows("1:1").Insert
.Range("A1") = "blah"
.UsedRange.AutoFilter Field:=12, Criteria1:="DC"
For Each cll In Intersect(.UsedRange.SpecialCells(xlCellTypeVisible), .Columns("J"))
If Not Dict.exists(cll.Value) Then Dict.Add cll.Value, cll.Value
Next cll
For Each entry In Dict
.UsedRange.AutoFilter Field:=10, Criteria1:=entry
.UsedRange.SpecialCells(xlCellTypeVisible).Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
ActiveSheet.Rows("1:1").Delete
ActiveSheet.Name = IIf(entry = "", "Blanks", entry)
Next entry
.UsedRange.AutoFilter 'removes autofilter
.Rows("1:1").Delete
End With
End Sub
[/vba]It will error if sheet names already exist, so only run once or delete sheets between runs, including 'Blanks'

[pdeshazier]

i can't thank you enough!!!!

[pdeshazier]

works on first two spreadsheets i run it on (doesn't matter in which order i run them) but never 3rd time. i am deleting 'blanks' s/sheet between each run...

[p45cal]

The sheets are named the same as what's left in column J after the DC filter is applied.
How are you running it on two or three sheets when it's only coded to run on one shee, viz.: "SUR DC PATIENTS LIST"?
You should be deleting sheets which are likely to recur (usually all the new sheets, including the 'blanks' one).
I'm only guessing what the error is - what is it?
Sometimes there can be things in cells which can't be used as sheet names. On what sheets is it falling over, what line of code? I can only guess - you tell me.

Edit.. oops, I just noticed your attachment.
With the data and macros as you had them in your attachment, the only error was it trying to produce a new Blanks sheet if one already existed. If I diligently deleted the blanks sheet between runs there was no error.
You have no blanks so I took out the creation of a blanks sheet - if you need it back, tell me.
If the sheet you attached is not a complete dataset, and there's a chance that different source sheets might have the same entry for a sheet name it will crash. Again, if this is possible we can write round that.

The attached has some modified code in to remove the creation of the blanks sheet.

[pdeshazier]

thank u so much!!!! i will try it w/the modified code.... again, u've been a lifesaver!

[pdeshazier]

worked like charm!!!! can't thank you enough.. took a lot of stress off me!