help with listing data
 

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.

help with listing data
 

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 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.

help with listing data
 

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

help with listing data
 

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 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.

.

help with listing data
 

Two options, either

Set CopyRange = Range("A2:K100")

or

CopyRange.SpecialCells(xlCellTypeVisible).EntireRo w.Copy _

Regards,
Per

"Bradly" skrev i meddelelsen
...
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 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.

.

help with listing data
 

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).EntireRo w.Copy _

Regards,
Per

"Bradly" skrev i meddelelsen
...
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 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.

.

.

help with listing data
 

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" skrev i meddelelsen
...
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).EntireRo w.Copy _

Regards,
Per

"Bradly" skrev i meddelelsen
...
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 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.

.

.

help with listing data
 

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" skrev i meddelelsen
...
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" skrev i meddelelsen
...
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).EntireRo w.Copy _

Regards,
Per

"Bradly" skrev i meddelelsen
...
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 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.

.

.

.

.

help with listing data
 

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
.

help with listing data
 

Change the line of code to:

CopyRange.Rows(r.Row).Copy DestCell

Then it should work....

"Bradly" skrev i meddelelsen
...
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" skrev i meddelelsen
...
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" skrev i meddelelsen
...
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).EntireRo w.Copy _

Regards,
Per

"Bradly" skrev i meddelelsen
...
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
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.

.

.

.

.

help with listing data
 

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" skrev i meddelelsen
...
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" skrev i meddelelsen
...
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" skrev i meddelelsen
...
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).EntireRo w.Copy _

Regards,
Per

"Bradly" skrev i meddelelsen
...
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
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.

.

.

.

.

.