Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . . . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pulling data from one sheet and listing selected data in another | Excel Worksheet Functions | |||
listing and selecting data | Excel Worksheet Functions | |||
Listing data | Excel Discussion (Misc queries) | |||
selecting and listing data | Excel Worksheet Functions | |||
Data mix and listing | Excel Programming |