VBA macro looping through multiple worksheets and returning exact matches <p>I have run through the relevant topics on the Internet, however I cannot find a solution to the problem I have encountered. I am working on a macro which would copy relevant data from one workbook into a newly created sheet in another workbook and then loop through the remaining worksheets of the latter to find exact matches to the data in this newly created sheet. The part in which I copy and paste the data works fine, however, when it comes to looping through worksheets an error occurs. </p> <p>I worked up multiple versions of this macro to see whether different solutions would work, however, actually none seems to work. I the destination workbook, the worksheet contain data tickers (sort of an id) in column A, the measure of data relevance in column B and names of the variables in column C. </p> <p>What I am trying to do is, after copying and pasting the data to a newly created sheet - where the data tickers are contained in column L, loop through all the default sheets in the destination workbook to check whether the tickers in column L of the newly created sheet overlap with the tickers in column A of the remainig worksheets, and, if so, copy the variable name from column C of the relevant worksheet into the newly created worksheet column M. The newly created worksheet is called "Settings" and contains headers in row 1 (it also consists of about 110 rows), the remaining worksheets contain no headers (and have 70 rows maximum). </p> <p>The macro looks like this:</p> <pre><code>Sub match1() Dim listwb As Workbook, mainwb As Workbook Dim FolderPath As String Dim fname As String Dim sht As Worksheet Dim ws As Worksheet, oput As Worksheet Dim oldRow As Integer Dim Rng As Range Dim ws2Row As Long Set mainwb = Application.ThisWorkbook With mainwb Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Settings" Set oput = Sheets("Settings") End With FolderPath = "C:\VBA\" fname = Dir(FolderPath &amp; "spr.xlsx") With Application Set listwb = .Workbooks.Open(FolderPath &amp; fname) End With Set sht = listwb.Worksheets(1) With sht .UsedRange.Copy End With mainwb.Activate With oput .Range("A1").PasteSpecial End With For Each ws In ActiveWorkbook.Worksheets If ws.Name &lt;&gt; "Settings" Then ws2Row = ws.Range("A" &amp; Rows.Count).End(xlUp).Row Set Rng = ws.Range("A:C" &amp; ws2Row) For oldRow = 2 To 110 Worksheets("Settings").Cells(oldRow, 13) = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Worksheets("Settings").Cells(oldRow, 12), Rng, 3, False), "") Next oldRow End If Next ws End Sub </code></pre> <p>Alternative version looks like this (skipping the copy-paste part):</p> <pre><code> mainwb.Activate With oput .Range("A1").PasteSpecial End With For Each ws In ActiveWorkbook.Worksheets If ws.Name &lt;&gt; "Settings" Then i = 1 For oldRow = 2 To 110 For newRow = 1 To 70 If StrComp((Worksheets("Settings").Cells(oldRow, 12).Text), (ws.Cells(newRow, 1).Text), vbTextCompare) &lt;&gt; 0 Then i = oldRow Worksheets("Settings").Cells(i, 13) = " " Else Worksheets("Settings").Cells(i, 13) = ws.Cells(newRow, 3) i = i + 1 Exit For End If Next newRow Next oldRow End If Next ws </code></pre> <p>When I launch the first version of the macro I get an error:</p> <blockquote> <p>Run-time error '1004':</p> <p>Method 'Range' of object '_Worksheet' failed</p> </blockquote> <p>Debugging highlights the part:</p> <pre><code>Set Rng = ws.Range("A:C" &amp; ws2Row) </code></pre> <p>When I run the second version of the macro the error message reads:</p> <blockquote> <p>Run-time error '9':</p> <p>Subscript out of range</p> </blockquote> <p>Debugging highlights the part:</p> <pre><code>If StrComp((Worksheets("Settings").Cells(oldRow, 12).Text), (ws.Cells(newRow, 1).Text), vbTextCompare) &lt;&gt; 0 Then </code></pre> <p>I suspect the problem is the definition and use of the ws (Worksheet) object. I am confused now because I use VBA a lot and I've done tasks much harder than this one. And yet I still can't solve the problem. Could you please suggest some solution. I will appreciate your help. </p>