How to copy data from one worksheet to another to a specific row

Question

To all, thanks for your time in advance.

we already have working code to move data from one wrksht to another with vb in excel.

we use:

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

This places our selected data on the last open row of sheet 2

Is it possible to , instead of the last row, Search for a reference number from the first sheet that is already on the second sheet , lets say Cell G3. use the information from the first sheet in cell g3 and look for it on the second sheet.

Once that row is found ( the G3 data from the first sheet will be in column A of the second sheet)

Now apply data to that row where applicable.

any help would be appreciated.

2/22/19

Here is my response.

thankyou for taking the time

I have put something together but wanted to run it by before executing

[code]

 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

[/code]

I'm am a little unsure about this row

[code]

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

[/code]

the match sheets 1 on the first workbook is called worksheet

and on the second workbook where the search is happening on the first column the sheet is called orderlog

thanks

First time user so I am not sure if I'm putting the response in the right place. — WhatWereUThinking
Your code formatting is badly readable :) but editing your question with additional information or code is the correct way. I edited my answer to give you a detailed help I hope. If it helps, please consider to mark it as answer as explained here: What should I do when someone answers my question?. If it doesn't help, please comment directly below it. — Asger

Asger Asger · Accepted Answer · 2019-02-22T14:59:41

You can find the matching row with 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

How to copy data from one worksheet to another to a specific row

1 Answers