I need your help urgently. i am very new to VBA so please forgive me if i ask any noob question. I have written a small VBA code as below. I am getting error Run-time '1004': Method Range of object worksheet fail on the row If ws.Range("B") = "Fail" Then. Please check what i have done wrong in the code and how can i get the desired result
What i am trying to achieve with the code is
I have multiple excel sheets in one workbook. let says sheet1, sheet2 and so on... I also have one sheet "SNAGS"
I have three columns in each sheets except SNAGS Sheet(Description, Result and Notes)
so for each description user can either put pass or fail in result column. What i want to do is in the whole workbook if any cell has a value "Fail", the corresponding value of description cell should be copied in a sheet "SNAGS.
Option Explicit Sub Snags() Dim ws As Worksheet, Snags As Worksheet Dim lr As Long, lrSnags As Long, i As Long Set Snags = Worksheets("Snags") Application.ScreenUpdating = False For Each ws In Worksheets If ws.Name <> "Snags" Then lr = ws.Range("A" & Rows.Count).End(xlUp).Row For i = 2 To lr lrSnags = Snags.Range("A" & Rows.Count).End(xlUp).Row + 1 If ws.Range("B") = "Fail" Then ws.Range("A" & i).Copy Snags.Range("A" & lrSnags).PasteSpecial xlPasteValues End If Next i End If Next ws Application.CutCopyMode = False Application.ScreenUpdating = True MsgBox "Complete" End Sub