如何将数据从一个工作表复制到另一个工作表到特定行

Question

对所有人，谢谢你提前的时间。

我们已经有了工作代码，可以在Excel中用vb将数据从一个wrksht移动到另一个wrksht。

Set lastrow = Sheets ("SR log").Cells(Rows.Count, 1).End(x1UP)
    With LastRow

如有任何帮助，我们将不胜感激。

2/22/19

以下是我的回应。

 Private Sub CommandButton2_Click()




    Workbooks.Open Filename:="G:\General\COVER  SHEET_Protective\Protective     Packaging Order Log.xlsm", Password:="PP", WriteResPassword:="PP"





   Dim FoundRow As Variant
   FoundRow = Application.Match(Sheets(1).Range("G3"), Sheets(2).Columns(1), 0)
    If IsNumeric(FoundRow) Then
    With FoundRow
        ' found, use FoundRow like LastRow before
    End With
   Else
    ' not found :(
    End If



 .Offset(1).Font.Size = 14

                .Offset(1, 9) = ws.[I10]
                .Offset(1, 10) = ws.[I11]

     End Sub

 Application.Match(Sheets(1).Range("G3"), Sheets(2).Columns(1), 0)

在第二个工作簿上，在第一列上进行搜索时，工作表称为orderlog

谢谢

Answer 1

您可以通过application.match找到匹配行：

Private Sub CommandButton2_Click()
    Dim wb1 As Workbook         ' first workbook
    Dim wb2 As Workbook         ' second workbook
    Dim wsCheck As Worksheet    ' sheet in the first workbook
    Dim wsOrderlog As Worksheet ' sheet in the second workbook

    ' address the first workbook and its sheet
    ' if this VBA-code is in the frist workbook, it's "ThisWorkbook"
    Set wb1 = ThisWorkbook
    Set wsCheck = wb1.Worksheets("Worksheet")

    ' check, if second workbook is already open
    For Each wb2 In Workbooks
        If wb2.Name = "Protective Packaging Order Log.xlsm" Then Exit For
    Next wb2

    ' if not already open, then open it, and address its sheet also
    If wb2 Is Nothing Then
        Set wb2 = Workbooks.Open( _
            Filename:="G:\General\COVERSHEET_Protective\Protective Packaging Order Log.xlsm", _
            Password:="PP", _
            WriteResPassword:="PP")
    End If
    Set wsOrderlog = wb2.Worksheets("orderlog")

    ' search a value from the first workbook's sheet within second workbook's sheet
    Dim FoundRow As Variant
    FoundRow = Application.Match(wsCheck.Range("G3").Value, wsOrderlog.Range("A:A"), 0)
    If IsNumeric(FoundRow) Then ' if found
        ' please adapt to your needs:
        wsOrderlog.Cells(FoundRow, 1).Font.Size = 14
        wsOrderlog.Cells(FoundRow, 9).Value = wsCheck.Range("I10").Value
        wsOrderlog.Cells(FoundRow, 10).Value = wsCheck.Range("I11").Value
    Else
        MsgBox "Sorry, the value in cell G3" & vbLf & _
            wsCheck.Range("G3").Value & vbLf & _
            "could not be found in orderlog column A."
    End If

    ' close the second workbook (Excel will ask, if to save)
    wb2.Close
End Sub