Macro to lookup a value in a Sheet and find that value in another sheet and Copy paste

What are the sheet names? Which cell in sheet2 contains the variable value? In which columns in the other sheet do you want to search for the value? Where do you want to paste all the found rows?

Range B9:K in which sheet, Consolidated_RC or Data? Do you want to copy the entirerow or just from column B to K?

This macro assumes that the Consolidated_RC sheet has headers in row 1 and the data starts at row 2.

Sub copyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Consolidated_RC").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim foundVal As Range
    Set foundVal = Sheets("Consolidated_RC").Range("C:C").Find(Sheets("Data").Range("B4").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        Sheets("Consolidated_RC").Range("C1:C" & LastRow).AutoFilter Field:=1, Criteria1:=foundVal
        Sheets("Consolidated_RC").Range("B2:K" & LastRow).SpecialCells(xlCellTypeVisible).copy Sheets("Data").Range("B9")
    End If
    If Sheets("Consolidated_RC").AutoFilterMode = True Then Sheets("Consolidated_RC").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

Try:

Sub copyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Consolidated_RC").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim foundVal As Range
    Set foundVal = Sheets("Consolidated_RC").Range("C:C").Find(Sheets("Data").Range("B4").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        Sheets("Consolidated_RC").Range("C1:C" & LastRow).AutoFilter Field:=1, Criteria1:=foundVal
        Sheets("Consolidated_RC").Range("B2:K" & LastRow).SpecialCells(xlCellTypeVisible).Copy
        Sheets("Data").Range("B9").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
    If Sheets("Consolidated_RC").AutoFilterMode = True Then Sheets("Consolidated_RC").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

Last week you have helped me by writing a code for me. The code works great. But I want to make a minor change. Some of the rows and columns in the the sheet CONSOLIDATED_RC I want to protect. (The sheet is protected). When I run the Macro gives me error:
Run-time error '1004'
Unable to set the autoFilterMode Property of the worksheet class.

The protection does not allow the macro to remove the filter. Can you please modify the code to the perform the action on the protected Consolidated_RC sheet. The code is given as under:

Sub copyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Consolidated_RC").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim foundVal As Range
    Set foundVal = Sheets("Consolidated_RC").Range("D:D").Find(Sheets("Data").Range("C3").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        Sheets("Consolidated_RC").Range("D1:D" & LastRow).AutoFilter Field:=1, Criteria1:=foundVal
        Sheets("Consolidated_RC").Range("B2:M" & LastRow).SpecialCells(xlCellTypeVisible).Copy
        Sheets("Data").Range("B9").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
    If Sheets("Consolidated_RC").AutoFilterMode = True Then Sheets("Consolidated_RC").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

Thanks in Advance.

Khalid

This macro assumes that the sheet is protected without a password. If you have used a password, please let me know and I will modify the macro.

Sub copyRange()
    Application.ScreenUpdating = False
    Sheets("Consolidated_RC").Unprotect
    Dim LastRow As Long
    LastRow = Sheets("Consolidated_RC").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim foundVal As Range
    Set foundVal = Sheets("Consolidated_RC").Range("D:D").Find(Sheets("Data").Range("C3").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        Sheets("Consolidated_RC").Range("D1:D" & LastRow).AutoFilter Field:=1, Criteria1:=foundVal
        Sheets("Consolidated_RC").Range("B2:M" & LastRow).SpecialCells(xlCellTypeVisible).Copy
        Sheets("Data").Range("B9").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
    If Sheets("Consolidated_RC").AutoFilterMode = True Then Sheets("Consolidated_RC").AutoFilterMode = False
    Sheets("Consolidated_RC").Protect
    Application.ScreenUpdating = True
End Sub

Sub copyRange()
    Application.ScreenUpdating = False
    Sheets("Consolidated_RC").Unprotect Password:="#09436"
    Dim LastRow As Long
    LastRow = Sheets("Consolidated_RC").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim foundVal As Range
    Set foundVal = Sheets("Consolidated_RC").Range("D:D").Find(Sheets("Data").Range("C3").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        Sheets("Consolidated_RC").Range("D1:D" & LastRow).AutoFilter Field:=1, Criteria1:=foundVal
        Sheets("Consolidated_RC").Range("B2:M" & LastRow).SpecialCells(xlCellTypeVisible).Copy
        Sheets("Data").Range("B9").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
    If Sheets("Consolidated_RC").AutoFilterMode = True Then Sheets("Consolidated_RC").AutoFilterMode = False
    Sheets("Consolidated_RC").Protect Password:="#09436"
    Application.ScreenUpdating = True
End Sub