Prev: freeze panel in a userform spreadsheet
Next: Delete all rows except
From: Bradly on 19 Feb 2010 17:01
I'm afraid I have a stupid question. How do I set up the cell with the
client ID# (in my example, 123456) as a variable? Will your suggestion work
if I have thousands of different client ID#s?

Thanks--sorry for getting back so late after your reply.


"marcus" wrote:

> 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: Per Jessen on 20 Feb 2010 02:41
Change the line of code to:

CopyRange.Rows(r.Row).Copy DestCell

Then it should work....

"Bradly" <Bradly(a)discussions.microsoft.com> skrev i meddelelsen
news:B449C8BA-FCC0-4378-9DB8-29BAAC3A989F(a)microsoft.com...
> I just tried running your suggestion and I get an error box. It reads
> "Run-time error '13': Type mismatch" and it is highlighting the line of
> code
> for...
>
> CopyRange.Rows(r).Copy DestCell
>
>
>
> "Per Jessen" wrote:
>
>> Try this (not tested):
>>
>> Dim FilterRange As Range
>> Dim CopyRange As Range
>> Dim DestCell As Range
>>
>> Set FilterRange = Range("I1:I30000") 'Header in row
>> Set CopyRange = Range("A1:L30000")
>>
>> FilterRange.AutoFilter Field:=1, Criteria1:="F"
>> Set CopyRange = CopyRange.SpecialCells(xlCellTypeVisible)
>> Set DestCell = Worksheets("F Cases").Range("A3")
>>
>> For Each r In CopyRange.Rows
>> CopyRange.Rows(r).Copy DestCell
>> Set DestCell = DestCell.Offset(5, 0)
>> Next
>> Application.CutCopyMode = False
>> Sheets("A List").Activate
>> Selection.AutoFilter
>> Application.Goto Reference:="R1C1"
>> Sheets("F Cases").Activate
>> Application.Goto Reference:="R1C1"
>>
>> Regards,
>> Per
>>
>> "Bradly" <Bradly(a)discussions.microsoft.com> skrev i meddelelsen
>> news:C936B277-A084-4402-825E-85DEE8635C74(a)microsoft.com...
>> > Another question...is it possible to adapt the following to paste the
>> > rows
>> > on, for example, every 5th line? It currently filters and copies onto
>> > the
>> > destination sheet one row after the other. What I would like to get is
>> > after
>> > filtering from the A List, the first case is pasted on row 3 of the F
>> > Cases
>> > sheet, the 2nd case is pasted on the 8th row, the 3rd case is pasted on
>> > the
>> > 13th row, etc.
>> >
>> > Dim FilterRange As Range
>> > Dim CopyRange As Range
>> > Set FilterRange = Range("I1:I30000") 'Header in row
>> > Set CopyRange = Range("A1:L30000")
>> > FilterRange.AutoFilter Field:=1, Criteria1:="F"
>> > CopyRange.SpecialCells(xlCellTypeVisible).Copy _
>> > Destination:=Worksheets("F Cases").Range("A3")
>> > Application.CutCopyMode = False
>> > Sheets("A List").Activate
>> > Selection.AutoFilter
>> > Application.Goto Reference:="R1C1"
>> > Sheets("F Cases").Activate
>> > Application.Goto Reference:="R1C1"
>> >
>> > Thanks.
>> >
>> >
>> > "Per Jessen" wrote:
>> >
>> >> I think this is what you want:
>> >>
>> >> Sub FilterCopyAList()
>> >> '
>> >> Dim FilterRange As Range
>> >> Dim CopyRange As Range
>> >> Dim MasterWbk As Workbook
>> >> Dim TargetWbk As Workbook
>> >>
>> >> Set MasterWbk = Workbooks("Master File.xls")
>> >> With MasterWbk.Worksheets("2010 Case List")
>> >> Set FilterRange = .Range("C3:C500") 'Header in row
>> >> Set CopyRange = .Range("A3:I300")
>> >> End With
>> >>
>> >> FilterRange.AutoFilter Field:=1, Criteria1:="A"
>> >> CopyRange.SpecialCells(xlCellTypeVisible).Copy _
>> >> Destination:=ThisWorkbook.Worksheets("A List").Range("A3")
>> >>
>> >> 'Application.CutCopyMode = False
>> >> 'Sheets("2010 Case List").Activate
>> >> FilterRange.AutoFilter
>> >> 'Range("A1").Select
>> >> End Sub
>> >>
>> >> Regards,
>> >> Per
>> >>
>> >> "Bradly" <Bradly(a)discussions.microsoft.com> skrev i meddelelsen
>> >> news:682A8BC7-9A93-4A82-A3A9-6E607D5449F8(a)microsoft.com...
>> >> > I have another question. Here is what is working now:
>> >> >
>> >> > Sub FilterCopyAList()
>> >> > '
>> >> > Sheets("2010 Case List").Activate
>> >> > Dim FilterRange As Range
>> >> > Dim CopyRange As Range
>> >> > Set FilterRange = Range("C3:C500") 'Header in row
>> >> > Set CopyRange = Range("A3:I300")
>> >> > FilterRange.AutoFilter Field:=1, Criteria1:="A"
>> >> > CopyRange.SpecialCells(xlCellTypeVisible).Copy _
>> >> > Destination:=Worksheets("A List").Range("A3")
>> >> > Application.CutCopyMode = False
>> >> > Sheets("2010 Case List").Activate
>> >> > Selection.AutoFilter
>> >> > Application.Goto Reference:="R1C1"
>> >> > End Sub
>> >> >
>> >> > How can this be adapted if I want to filter and copy from a
>> >> > different
>> >> > file
>> >> > called the "Master File" and paste back into the "A List" of the
>> >> > current
>> >> > file
>> >> > called "2010 Cases"?
>> >> >
>> >> > Also, is it possible to set the CopyRange for multiple ranges like
>> >> > "A3:F10"
>> >> > and "H3:J10" at one time?
>> >> >
>> >> > Thanks.
>> >> >
>> >> >
>> >> > "Per Jessen" wrote:
>> >> >
>> >> >> 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.
>> >> >> >>
>> >> >> >> .
>> >> >> >>
>> >> >> .
>> >> >>
>> >> .
>> >>
>> .
>>
From: Bradly on 21 Feb 2010 14:22
It works fine. Thank you very much.


"Per Jessen" wrote:

> Change the line of code to:
>
> CopyRange.Rows(r.Row).Copy DestCell
>
> Then it should work....
>
> "Bradly" <Bradly(a)discussions.microsoft.com> skrev i meddelelsen
> news:B449C8BA-FCC0-4378-9DB8-29BAAC3A989F(a)microsoft.com...
> > I just tried running your suggestion and I get an error box. It reads
> > "Run-time error '13': Type mismatch" and it is highlighting the line of
> > code
> > for...
> >
> > CopyRange.Rows(r).Copy DestCell
> >
> >
> >
> > "Per Jessen" wrote:
> >
> >> Try this (not tested):
> >>
> >> Dim FilterRange As Range
> >> Dim CopyRange As Range
> >> Dim DestCell As Range
> >>
> >> Set FilterRange = Range("I1:I30000") 'Header in row
> >> Set CopyRange = Range("A1:L30000")
> >>
> >> FilterRange.AutoFilter Field:=1, Criteria1:="F"
> >> Set CopyRange = CopyRange.SpecialCells(xlCellTypeVisible)
> >> Set DestCell = Worksheets("F Cases").Range("A3")
> >>
> >> For Each r In CopyRange.Rows
> >> CopyRange.Rows(r).Copy DestCell
> >> Set DestCell = DestCell.Offset(5, 0)
> >> Next
> >> Application.CutCopyMode = False
> >> Sheets("A List").Activate
> >> Selection.AutoFilter
> >> Application.Goto Reference:="R1C1"
> >> Sheets("F Cases").Activate
> >> Application.Goto Reference:="R1C1"
> >>
> >> Regards,
> >> Per
> >>
> >> "Bradly" <Bradly(a)discussions.microsoft.com> skrev i meddelelsen
> >> news:C936B277-A084-4402-825E-85DEE8635C74(a)microsoft.com...
> >> > Another question...is it possible to adapt the following to paste the
> >> > rows
> >> > on, for example, every 5th line? It currently filters and copies onto
> >> > the
> >> > destination sheet one row after the other. What I would like to get is
> >> > after
> >> > filtering from the A List, the first case is pasted on row 3 of the F
> >> > Cases
> >> > sheet, the 2nd case is pasted on the 8th row, the 3rd case is pasted on
> >> > the
> >> > 13th row, etc.
> >> >
> >> > Dim FilterRange As Range
> >> > Dim CopyRange As Range
> >> > Set FilterRange = Range("I1:I30000") 'Header in row
> >> > Set CopyRange = Range("A1:L30000")
> >> > FilterRange.AutoFilter Field:=1, Criteria1:="F"
> >> > CopyRange.SpecialCells(xlCellTypeVisible).Copy _
> >> > Destination:=Worksheets("F Cases").Range("A3")
> >> > Application.CutCopyMode = False
> >> > Sheets("A List").Activate
> >> > Selection.AutoFilter
> >> > Application.Goto Reference:="R1C1"
> >> > Sheets("F Cases").Activate
> >> > Application.Goto Reference:="R1C1"
> >> >
> >> > Thanks.
> >> >
> >> >
> >> > "Per Jessen" wrote:
> >> >
> >> >> I think this is what you want:
> >> >>
> >> >> Sub FilterCopyAList()
> >> >> '
> >> >> Dim FilterRange As Range
> >> >> Dim CopyRange As Range
> >> >> Dim MasterWbk As Workbook
> >> >> Dim TargetWbk As Workbook
> >> >>
> >> >> Set MasterWbk = Workbooks("Master File.xls")
> >> >> With MasterWbk.Worksheets("2010 Case List")
> >> >> Set FilterRange = .Range("C3:C500") 'Header in row
> >> >> Set CopyRange = .Range("A3:I300")
> >> >> End With
> >> >>
> >> >> FilterRange.AutoFilter Field:=1, Criteria1:="A"
> >> >> CopyRange.SpecialCells(xlCellTypeVisible).Copy _
> >> >> Destination:=ThisWorkbook.Worksheets("A List").Range("A3")
> >> >>
> >> >> 'Application.CutCopyMode = False
> >> >> 'Sheets("2010 Case List").Activate
> >> >> FilterRange.AutoFilter
> >> >> 'Range("A1").Select
> >> >> End Sub
> >> >>
> >> >> Regards,
> >> >> Per
> >> >>
> >> >> "Bradly" <Bradly(a)discussions.microsoft.com> skrev i meddelelsen
> >> >> news:682A8BC7-9A93-4A82-A3A9-6E607D5449F8(a)microsoft.com...
> >> >> > I have another question. Here is what is working now:
> >> >> >
> >> >> > Sub FilterCopyAList()
> >> >> > '
> >> >> > Sheets("2010 Case List").Activate
> >> >> > Dim FilterRange As Range
> >> >> > Dim CopyRange As Range
> >> >> > Set FilterRange = Range("C3:C500") 'Header in row
> >> >> > Set CopyRange = Range("A3:I300")
> >> >> > FilterRange.AutoFilter Field:=1, Criteria1:="A"
> >> >> > CopyRange.SpecialCells(xlCellTypeVisible).Copy _
> >> >> > Destination:=Worksheets("A List").Range("A3")
> >> >> > Application.CutCopyMode = False
> >> >> > Sheets("2010 Case List").Activate
> >> >> > Selection.AutoFilter
> >> >> > Application.Goto Reference:="R1C1"
> >> >> > End Sub
> >> >> >
> >> >> > How can this be adapted if I want to filter and copy from a
> >> >> > different
> >> >> > file
> >> >> > called the "Master File" and paste back into the "A List" of the
> >> >> > current
> >> >> > file
> >> >> > called "2010 Cases"?
> >> >> >
> >> >> > Also, is it possible to set the CopyRange for multiple ranges like
> >> >> > "A3:F10"
> >> >> > and "H3:J10" at one time?
> >> >> >
> >> >> > Thanks.
> >> >> >
> >> >> >
> >> >> > "Per Jessen" wrote:
> >> >> >
> >> >> >> 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.
> >> >> >> >>
> >> >> >> >> .
> >> >> >> >>
> >> >> >> .
> >> >> >>
> >> >> .
> >> >>
> >> .
> >>
> .
>
First  |  Prev  | 
Pages: 1 2 3
Prev: freeze panel in a userform spreadsheet
Next: Delete all rows except