如何使用特定单词在excel中复制一行并粘贴到另一个excel表? [英] How to copy a line in excel using a specific word and pasting to another excel sheet?

问题描述

我检查了一堆不同的帖子，但似乎无法找到我正在寻找的确切代码.此外，我以前从未使用过 VBA，所以我试图从其他帖子中获取代码并输入我的信息以使其正常工作.还没有运气.在工作中，我们在 Excel 中有一个工资系统.我正在尝试搜索我的名字 "Clarke, Matthew"，然后复制该行并将其粘贴到我保存在桌面上的工作簿 "Total hours".

I have checked a bunch of different posts and can't seem to find the exact code I am looking for. Also I have never used VBA before so I'm trying to take codes from other posts and input my info for it to work. No luck yet. At work we have a payroll system in Excel. I am trying to search for my name "Clarke, Matthew" and then copy that row and paste it to the workbook I have saved on my desktop "Total hours".

推荐答案

试用和测试

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

快照

这篇关于如何使用特定单词在excel中复制一行并粘贴到另一个excel表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！