How to copy a line in excel using a specific word and pasting to another excel sheet?

by

CODE

Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim copyFrom As Range
    Dim lRow As Long '<~~ Not Integer. Might give you error in higher versions of excel
    Dim strSearch As String
    
    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Worksheets("yourSheetName")
    
    strSearch = "Clarke, Matthew"
    
    With ws1

        '~~> Remove any filters
        .AutoFilterMode = False

        '~~> I am assuming that the names are in Col A
        '~~> if not then change A below to whatever column letter
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
       
        With .Range("A1:A" & lRow)
            .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
            Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With
        
        '~~> Remove any filters
        .AutoFilterMode = False
    End With
    
    '~~> Destination File
    Set wb2 = Application.Workbooks.Open("C:\Sample.xlsx")
    Set ws2 = wb2.Worksheets("Sheet1")
    
    With ws2
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 1
        End If
    
        copyFrom.Copy .Rows(lRow)
    End With
    
    wb2.Save
    wb2.Close
End Sub

SNAPSHOT

enter image description here

More Related Contents:

  1. Sequence of numbers based on start and end value of 2 cells [closed]
  2. Is there a way to crack the password on an Excel VBA Project?
  3. How do I declare a global variable in VBA?
  4. How to use the Implements in Excel VBA
  5. VBA code doesn’t run when cell is changed by a formula
  6. What is the difference between dim and set in vba
  7. How to set recurring schedule for xlsm file using Windows Task Scheduler
  8. Change date format using substitute or replace
  9. Getting the actual usedrange
  10. Delete a row in Excel VBA
  11. VBA: Convert Text to Number
  12. Iterating through populated rows
  13. How can I run VBA code each time a cell gets its value changed by a formula?
  14. Using VBA to detect which decimal sign the computer is using
  15. Referencing Excel Userform Control Name Value From String (VBA)
  16. Loop through each row of a range in Excel
  17. Fill formula down till last row in column
  18. How to create a range from two ranges in VBA?
  19. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  20. If string contains word from list, return value adjacent to list value
  21. Refreshing all the pivot tables in my excel workbook with a macro
  22. Countif With Multiple OR Criteria
  23. Vlookup to copy color of a cell
  24. excel vba freeze pane without select
  25. Converting a string representation of a constant into a constant?
  26. Macro to export MS Word tables to Excel sheets
  27. VBA WinHTTP to download file from password proteced https website
  28. Create folder path if does not exist (saving from VBA)
  29. Access files with long paths (over 260)
  30. Concatenate multiple ranges using vba

Leave a Comment