Trying to find all instances of a client name and copy the range into a new workbook

[EVANWIT84]

Hi All,

I'm trying to find all instances of a client name and copy the range into a new workbook where I want to perform a calculation. These are my headers and I want to do a find on the client account number, such as 5 and then copy the range. My existing range runs from A1:I71573. I have about 900 client Account Numbers to loop thru. Any suggestions on how to use the Find Function to then find all and copy the dynamic range? The workbook that I will then copy these values to is called "CalcSheet".

Client Account Number

Client Master Account Name

Clent Account Name

Period

Contribution

Withdrawal

Ending Market Value

Net Return

Inception Date

Thanks,
Evan

 

[JB2020]

Hi Evan

Using advanced filter might be the easiest solution for this.

This is under Data>Advanced (Sort & Filter) in the ribbon. Make sure you select the sheet you want to copy to before clicking it.

https://support.microsoft.com/en-gb/help/2720580/using-advanced-filters-in-excel-2010

 

[EVANWIT84]

Hi Evan

Using advanced filter might be the easiest solution for this.

This is under Data>Advanced (Sort & Filter) in the ribbon. Make sure you select the sheet you want to copy to before clicking it.

https://support.microsoft.com/en-gb/help/2720580/using-advanced-filters-in-excel-2010

Thank you...this is what I wound up doing. It basically just copies the values I need.

Sub Many_Finds()

Dim CompId As Range
Dim i As Byte
Dim FirstMatch As Variant
Dim h As Long
Dim lstCell As Long

lstCell = [b1048567].End(xlUp).Row
i = 3

'Dim Start
'Start = VBA.Timer

Sheets("CalcSheet").Range("a1:i1000").ClearContents ' Destination
Set CompId = Sheet1.Range("A:I").Find("5", LookIn:=xlValues, lookat:=xlWhole)

If Not CompId Is Nothing Then
Sheets("CalcSheet").Range("B" & i).Value = CompId.Offset(, 7).Value
FirstMatch = CompId.Address
Do
Set CompId = Sheet1.Range("A:A").FindNext(CompId)
If CompId.Address = FirstMatch Then Exit Do
i = i + 1

Sheets("CalcSheet").Range("B" & i).Value = CompId.Offset(, 7).Value

Loop