View Full Version : Solved: SEARCH COLUMN FOR VALUE AND COPY ROW
pdeshazier
07-29-2010, 09:50 AM
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
07-29-2010, 05:35 PM
Make sure that sheet 'SUR DC PATIENTS LIST' has no autofilter, then run this: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
If you need it, I can attach a file.
pdeshazier
08-02-2010, 10:30 AM
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.:hi:
Make sure that sheet 'SUR DC PATIENTS LIST' has no autofilter, then run this: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
If you need it, I can attach a file.
pdeshazier
08-02-2010, 10:35 AM
:bow: thanks so much!!!!
pdeshazier
08-02-2010, 10:38 AM
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
08-02-2010, 11:33 AM
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 valueSub 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
It will error if sheet names already exist, so only run once or delete sheets between runs, including 'Blanks'
pdeshazier
08-02-2010, 11:40 AM
i can't thank you enough!!!!
pdeshazier
08-02-2010, 01:13 PM
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
08-02-2010, 02:45 PM
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
08-03-2010, 06:18 AM
thank u so much!!!! i will try it w/the modified code.... again, u've been a lifesaver!
pdeshazier
08-03-2010, 07:56 AM
worked like charm!!!! can't thank you enough.. took a lot of stress off me!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.