Find and Copy

[lakshmipathi123]

Hi Experts,

Could you please provide me macro coding for Find and copy.

Here is an example

I have data around 15000 lines. I need segregate the data into 4 accounts. i.e.20310-0000,20320-0000,20110- and 20280.

Macro should find macro first line 20310-0000 copy till last line of 20310-0000 and paste into sheet 2

Range("A1").Select
    Cells.Find(What:="Liability Account : 20110-", After:=ActiveCell, _
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
   
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("20110 - Detailed Invoice Aging").Select
    Range("A2").Select
    ActiveSheet.Paste

Thank you,
Lakshmipathi

 

[Andrew Poulsom]

Can't you use AutoFilter and copy/paste the visible rows?

 

[lakshmipathi123]

Hi Andrew,

Thanks for your reply.

Auto filter cant use bcoz it will not copy all the data....i need to copy all the data from first to last line. Will you able to help me with my request

Thank you,

 

[lakshmipathi123]

Hi Andrew,

Will you able help with this as well.If Condition Macro Code link...i wanted to use two different ways to use the code.
http://www.mrexcel.com/forum/excel-questions/665368-if-condition-macro-code.html

 

[Andrew Poulsom]

In which column are the accounts?

 

[lakshmipathi123]

Hi Andrew,

Accounts will be in A column and related one account has Employee name,ID,Amount and aging. If we do filter will get only accounts. But i need Account+Employee name,ID,Amount and aging. Here is the code i used.

Range("A1").Select
    Cells.Find(What:="Liability Account : 20110-", After:=ActiveCell, _
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
   
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("20110 - Detailed Invoice Aging").Select
    Range("A2").Select
    ActiveSheet.Paste

 

[Andrew Poulsom]

Try:

Sub Test()
    With ActiveSheet.Range("A1").CurrentRegion
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:= _
            "=Liability Account : 20110-*", Operator:=xlAnd
        With .Parent.AutoFilter.Range
            With .Offset(1).Resize(.Rows.Count - 1)
                .Copy Sheets("20110 - Detailed Invoice Aging").Range("A2")
                .EntireRow.Delete
            End With
        End With
        .Parent.AutoFilterMode = False
    End With
End Sub

 

[lakshmipathi123]

Hi Andrew,

Filter mode will not work for me....i need something like below code. But below code is not working for me...any suggestions.

  If Cells.Find(What:="Liability Account : 20310-0000", After:=ActiveCell, _
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate Then
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireRow.Delete
    Else
    MsgBox "Nodata"
    End If
   
    If Cells.Find(What:="Liability Account : 20320-0000", After:=ActiveCell, _
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate Then
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireRow.Delete
    Else
    MsgBox "Nodata"
    End If

 

[Andrew Poulsom]

Why won't filter mode work for you? It works for me.

 

[lakshmipathi123]

Hi,

I tried Ur code but it is copying filtered data. I need to find this account "Liability Account : 20310-0000" and activate from that line copy till last line of 20310-0000 data. I dont know how to attach my excel sheet to this thread. That will make u better understand.Right now am working on this code.

Range("A1").Select
    Set foundcell = Cells.Find(What:="Liability Account : 20280-0000", After:=ActiveCell, _
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
   
    If foundcell Is Nothing Then
    Else
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    foundcell.Selection.EntireRow.Delete
    End If