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.
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.
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.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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.
Originally Posted by p45cal
thanks so much!!!!
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()Originally Posted by pdeshazier
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'
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
i can't thank you enough!!!!
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...
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.
Last edited by p45cal; 08-02-2010 at 03:42 PM.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
thank u so much!!!! i will try it w/the modified code.... again, u've been a lifesaver!
worked like charm!!!! can't thank you enough.. took a lot of stress off me!