help with listing data
in [Programming]
|
From: Bradly on 5 Feb 2010 23:25 I am using a COUNTIF function to count the number of times a client ID# is found in our master list of cases. This signifies that the particular client has that certain number of cases with us. The ID# and case information would be found spread out in the list, as opposed to being grouped together. Is there any way to actually find these instances, copy each one, and paste each in a separate worksheet? For example, the COUNTIF function shows that client ID# 123456 occurs 4 times, meaning this client has 4 cases. One case might be on row 13, the second on row 274, etc. Is there any way to set up a function, formula, or macro to read the entire list, pick out each of these 4 cases by client ID#, and paste them together in a separate worksheet? Thanks.
From: Per Jessen on 6 Feb 2010 00:02 Hi I would use an autofilter and filter for client ID#, then copy visible rows, like this: Sub FilterCopy() Dim FilterRange As Range Dim CopyRange As Range Set FilterRange = Range("A1:A100") 'Header in row Set CopyRange = Range("A2:A100") FilterRange.AutoFilter Field:=1, Criteria1:=123456 CopyRange.SpecialCells(xlCellTypeVisible).Copy _ Destination:=Worksheets("Sheet2").Range("A2") Application.CutCopyMode=False End Sub Regards, Per On 6 Feb., 05:25, Bradly <Bra...(a)discussions.microsoft.com> wrote: > I am using a COUNTIF function to count the number of times a client ID# is > found in our master list of cases. This signifies that the particular client > has that certain number of cases with us. The ID# and case information would > be found spread out in the list, as opposed to being grouped together. Is > there any way to actually find these instances, copy each one, and paste each > in a separate worksheet? > > For example, the COUNTIF function shows that client ID# 123456 occurs 4 > times, meaning this client has 4 cases. One case might be on row 13, the > second on row 274, etc. Is there any way to set up a function, formula, or > macro to read the entire list, pick out each of these 4 cases by client ID#, > and paste them together in a separate worksheet? > > Thanks.
From: marcus on 6 Feb 2010 00:22 Hi Bradley This runs like lightning. I would change the cell you want to use as your criteria to be a variable then its all down hill. Take care Marcus Sub findit() 'Make 123456 a variable Find_Range(123456, Columns("B"), xlFormulas, xlWhole). _ EntireRow.Copy Range("Sheet2!B65536").End(xlUp).Offset(1, 0).EntireRow End Sub Function Find_Range(Find_Item As Variant, _ Search_Range As Range, _ Optional LookIn As Variant, Optional LookAt As Variant, _ Optional MatchCase As Boolean) As Range Dim c As Range If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole If IsMissing(MatchCase) Then MatchCase = False With Search_Range Set c = .Find(What:=Find_Item, LookIn:=LookIn, _ LookAt:=LookAt, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=MatchCase, _ SearchFormat:=False) If Not c Is Nothing Then Set Find_Range = c firstAddress = c.Address Do Set Find_Range = Union(Find_Range, c) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With End Function
From: Bradly on 11 Feb 2010 16:40 I tried running this, an it only seems to give me the client ID# in the destination column. Is there any way to paste each entire row of data in the destination worksheet (it would take up columns A:K for each row)? "Per Jessen" wrote: > Hi > > I would use an autofilter and filter for client ID#, then copy visible > rows, like this: > > Sub FilterCopy() > Dim FilterRange As Range > Dim CopyRange As Range > Set FilterRange = Range("A1:A100") 'Header in row > Set CopyRange = Range("A2:A100") > FilterRange.AutoFilter Field:=1, Criteria1:=123456 > CopyRange.SpecialCells(xlCellTypeVisible).Copy _ > Destination:=Worksheets("Sheet2").Range("A2") > Application.CutCopyMode=False > End Sub > > Regards, > Per > > > On 6 Feb., 05:25, Bradly <Bra...(a)discussions.microsoft.com> wrote: > > I am using a COUNTIF function to count the number of times a client ID# is > > found in our master list of cases. This signifies that the particular client > > has that certain number of cases with us. The ID# and case information would > > be found spread out in the list, as opposed to being grouped together. Is > > there any way to actually find these instances, copy each one, and paste each > > in a separate worksheet? > > > > For example, the COUNTIF function shows that client ID# 123456 occurs 4 > > times, meaning this client has 4 cases. One case might be on row 13, the > > second on row 274, etc. Is there any way to set up a function, formula, or > > macro to read the entire list, pick out each of these 4 cases by client ID#, > > and paste them together in a separate worksheet? > > > > Thanks. > > . >
From: Per Jessen on 12 Feb 2010 13:40
Two options, either Set CopyRange = Range("A2:K100") or CopyRange.SpecialCells(xlCellTypeVisible).EntireRow.Copy _ Regards, Per "Bradly" <Bradly(a)discussions.microsoft.com> skrev i meddelelsen news:94D94F8E-8548-4BFE-B233-136675C04F62(a)microsoft.com... > I tried running this, an it only seems to give me the client ID# in the > destination column. Is there any way to paste each entire row of data in > the > destination worksheet (it would take up columns A:K for each row)? > > "Per Jessen" wrote: > >> Hi >> >> I would use an autofilter and filter for client ID#, then copy visible >> rows, like this: >> >> Sub FilterCopy() >> Dim FilterRange As Range >> Dim CopyRange As Range >> Set FilterRange = Range("A1:A100") 'Header in row >> Set CopyRange = Range("A2:A100") >> FilterRange.AutoFilter Field:=1, Criteria1:=123456 >> CopyRange.SpecialCells(xlCellTypeVisible).Copy _ >> Destination:=Worksheets("Sheet2").Range("A2") >> Application.CutCopyMode=False >> End Sub >> >> Regards, >> Per >> >> >> On 6 Feb., 05:25, Bradly <Bra...(a)discussions.microsoft.com> wrote: >> > I am using a COUNTIF function to count the number of times a client ID# >> > is >> > found in our master list of cases. This signifies that the particular >> > client >> > has that certain number of cases with us. The ID# and case information >> > would >> > be found spread out in the list, as opposed to being grouped together. >> > Is >> > there any way to actually find these instances, copy each one, and >> > paste each >> > in a separate worksheet? >> > >> > For example, the COUNTIF function shows that client ID# 123456 occurs 4 >> > times, meaning this client has 4 cases. One case might be on row 13, >> > the >> > second on row 274, etc. Is there any way to set up a function, >> > formula, or >> > macro to read the entire list, pick out each of these 4 cases by client >> > ID#, >> > and paste them together in a separate worksheet? >> > >> > Thanks. >> >> . >> |