I am recently trying to build a macro to copy data from diff sheets and paste them into a mastersheet. However, I have some problems with a part my of codes which are used to find the values in the diff sheets.
Here are my codes as follows:
Sub retreiveData(intelSht, updateSht, ctyName, i) Dim lastRow, ctyCol, lastCol, lRow As Variant 'get lastrow of the update sheet lRow = updateSht.Cells(Rows.Count, 1).End(xlUp).Row 'copy and paste over value from intel sheets Select Case i Case 0 'Month-Year ctyCol = Application.Match(ctyName, intelSht.Range("2:1"), 0) lastRow = intelSht.Cells(Rows.Count, ctyCol).End(xlUp).Row updateSht.Range(updateSht.Cells(3, 1), updateSht.Cells(lRow, 1)).Value = intelSht.Range(intelSht.Cells(20, ctyCol), intelSht.Cells(lastRow, ctyCol)).Value
This is the row with the error:
And ctyCol is the one having the error 2042, even though all my data rows do not have any N/A values. However, these rows contains formulaes, which I suspect might be the case of the issue.
Is there anyone that can help with this issue?
Thank you and have a great day!