Prev: freeze panel in a userform spreadsheet
Next: Delete all rows except
From: Bradly on 17 Feb 2010 09:15
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: Per Jessen on 17 Feb 2010 14:42
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 19 Feb 2010 13:10
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: Per Jessen on 19 Feb 2010 13:26
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 19 Feb 2010 15:35
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  |  Next  |  Last
Pages: 1 2 3
Prev: freeze panel in a userform spreadsheet
Next: Delete all rows except