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]

My code copies the data for account 20110 to another worksheet then deletes it. If you don't want to copy it remove that line. If you want to find a different account change the filter criteria.

 

[lakshmipathi123]

Hi Andrew,

I got it ....Please see below code. Thanks for your help

Copy

    Range("A1").Select
    Set foundcell = Cells.Find(What:="Liability Account : 20110-", After:=ActiveCell, _
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If foundcell Is Nothing Then
    MsgBox "No Data for Account 20110"
    Exit Sub
    Else
    foundcell.Activate
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("20110 - Detailed Invoice Aging").Select
    Range("A2").Select
    ActiveSheet.Paste
    End If

Delete

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