VBA Excel copy data from one workbook to another when specified string appears in the row

Question

I have the situation presented below in the image (Workbook 1):

and below (Workbook 2)

I want to copy my record from workbook 1 to workbook 2 if

in the Workbook 1 column A the string "surveyor" appears
the value from column B, which is exactly in the same row, where the string "suveyor" was found.

Then I would like to copy this value to my workbook 2.

I have prepared the code like this:

 Sub FrontsheetAdd3()
 Dim x As Worksheet, y As Worksheet, sPath As String
 Dim i As Long

 sPath = ThisWorkbook.Path & "\Survey_form.csv"
 Set x = Workbooks.Open(sPath)

 Set y = ActiveWorkbook.Sheets("Frontsheet") 'set to current worksheet name

'Name of the sheet is the same as Name of the workbook 1
 If x.Sheets("Survey_form").Range("A" & i).Value = "surveyor" Then
 x.Sheets("Survey_form").Rage("B" & i).Value = ("A" & i)
 y.Sheets("Frontsheet").Range("D34").PasteSpecial
 End If
 Next i

 End Sub

I have an error:

Method or data member not found

at the line

   If x.Sheets("Survey_form").Range("A" & i).Value = "surveyor" Then

UPDATE:

After changing my code, which now looks like this:

 Sub FrontsheetAdd3()
 Dim x As Workbook, y As Workbook, sPath As String
 Dim i As Long

 sPath = ThisWorkbook.Path & "\Survey_form.csv"
 Set x = Workbooks.Open(sPath)

 Set y = ActiveWorkbook.Sheets("Frontsheet") 'set to current worksheet name

 'Name of the sheet is the same as Name of the workbook 1
  For i = 1 To 40
  If x.Sheets("Survey_form").Range("A" & i).Value = "surveyor" 
   Then
  x.Sheets("Survey_form").Rage("B" & i).Value = ("A" & i)
  y.Sheets("Frontsheet").Range("D34").PasteSpecial
  End If
  Next i

  End Sub

At the line:

      Set y = ActiveWorkbook.Sheets("Frontsheet") 'set to current worksheet name

my active workbook (Workbook2), where the macro is meant to be is closing down and error Subscript out of range emerges.

What is missig then?

Try `Dim x As Workbook`, you had it declared as a sheet. You could use Find instead of a loop. — SJR, Nov 05 '21 at 11:28
Your code does not iterate at all. It misses something like `For i = 1 to...`. But the code will be much faster if you try using an array to iterate its elements... I supose, you know that `Set y = ActiveWorkbook.Sheets(...` refers the newly open csv file... — FaneDuru, Nov 05 '21 at 11:29
You do not need to open two workbooks, to get data from a .csv file. You can import the CSV file into a new sheet, merge the data, then delete the new sheet. See [How to import data from .csv into a sheet](https://stackoverflow.com/a/12197937/14958486). — GoWiser, Nov 05 '21 at 11:42
Is there a sheet named "Frontsheet" **in the csv open file**? If not, did you read my previous comment till the end? If it should belong to the previous open workbook move the line before `Set x = Workbooks.Open(sPath)`. — FaneDuru, Nov 05 '21 at 11:48
There are several issues. Using ActiveWorkbook may refer to the wrong workbook. Opening a CSV file, will not name the sheet for you (refer to the first sheet in the workbook instead), there is no loop, and PasteSpecial does not make sense, since nothing is copied. — GoWiser, Nov 05 '21 at 12:01
@FaneDuru the sheet named "Frontsheet" refers to my workbook 2. When I moved the Set y = ActiveWorkbook... upper than Set X = ... I am having the "Type misatch" error. — Geographos, Nov 05 '21 at 12:14
Then, did you do what I suggested? "**move the (erroring) line before `Set x = Workbooks.Open(sPath)`**... — FaneDuru, Nov 05 '21 at 12:15
Now I am having "Type mismatch" error, which points exactly to the Sey y = Active workbook.... line — Geographos, Nov 05 '21 at 12:16
You wrongly declared the variable. It should be `y As Worksheet`... But where from do you want copying to which sheet? Your code does nothing from this point of view and i cannot understand what you want accomplishing... Do you need copying from csv to the active sheet or vice versa? — FaneDuru, Nov 05 '21 at 12:21

score 1 · Accepted Answer · answered Nov 05 '21 at 12:26

Please, try the next adapted code. It will copy from the csv file in the active one and exit loop:

Sub FrontsheetAdd3()
 Dim x As Workbook, y As Worksheet, ws As Worksheet, sPath As String, i As Long

  sPath = ThisWorkbook.path & "\Survey_form.csv"

  Set y = ActiveWorkbook.Sheets("Frontsheet") 'set to current worksheet name
  Set x = Workbooks.Open(sPath): Set ws = x.Sheets(1)

  For i = 1 To 40
    If ws.Range("A" & i).value = "surveyor" Then
        y.Range("D34").value = ws.Rage("B" & i).value: Exit For
    End If
  Next i
End Sub

VBasic2008 · Answer 2 · 2021-11-05T12:33:20.520

A VBA Lookup

Use Option Explicit which forces you to declare all variables.
Use variables (more of them) to make the code more readable.
Use meaningful variable names: sPath is a great name while x and y used for workbooks are terrible.
Instead of the loop, use Application.Match.
You can basically copy in three ways: Copy, Copy with PasteSpecial or Copy by Assignment (dCell.Value = sCell.Value) the latter being the most efficient when copying only values.

Option Explicit

Sub FrontsheetAdd3()

    Dim dwb As Workbook: Set dwb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = dwb.Worksheets("Frontsheet")
    Dim dCell As Range: Set dCell = dws.Range("D34")
    
    Dim sPath As String: sPath = dwb.Path & "\Survey_form.csv"
    Dim swb As Workbook: Set swb = Workbooks.Open(sPath)
    Dim sws As Worksheet: Set sws = wb.Worksheets("Survey_form")
    ' Determine the position of the first occurence of "surveyor" in column 'A'.
    Dim sIndex As Variant
    sIndex = Application.Match("surveyor", sws.Columns("A"), 0)
    
    If IsNumeric(sIndex) Then ' "suveyor" was found
        Dim sCell As Range: Set sCell = sws.Rows(sIndex).Columns("B")
        dCell.Value = sCell.Value
    Else ' "surveyor" was not found
        dCell.Value = ""
    End If

    swb.Close SaveChanges:=False
    'dwb.Save

End Sub

VBA Excel copy data from one workbook to another when specified string appears in the row

2 Answers2

A VBA Lookup