• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Value of one cell copied depending on value of other cell

Madz1101

New Member
Hello guys,

I need your help. 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)

example

Sheet 1

Description Result Notes

Is all OK Pass/Fail No New notes
Are you OK Pass/Fail Yes i am ok


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" which is already added. I have done this while using single sheet with a strange formula as below.

IFERROR(INDEX([values I want to return],SMALL(IF([Criteria Array]=[Criteria],ROW([Array I are looking up])),ROW()),1),"")


unfortunately to apply on whole workbook i will have to do VBA, which i am not able to. can some one please help me out here
 
Code:
Option Explicit

Sub Snags()
    Dim ws As Worksheet, Snags As Worksheet
    Dim lr As Long, lrSnags As Long, i As Long
    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
 
Hello,

Thanks for reply. However I am getting an error when i run the macro.

Run-time error '91':

Object Variable or block variable not set

on the row

lrSnags = Snags.Range("A" & Rows.Count).End(xlUp).Row + 1

I am sorry as i am very new to VBA. I tried looking over internet but couldn't solve

Thansk[/Code]
 
Last edited:
I resolved the issue by using by setting snags using the below

Set Snags = Worksheets("Snags")

now i am getting erro on

If ws.Range("B") = "Fail" Then

Run-time error '1004':

Method'Range' of object '_worksheet' failed

Please anyone help
 
Back
Top