VBA: copy row from one sheet to another using a specific cell as reference

[vimalashankar]

Hi everyone,

I am having a problem with my formula below. I am trying to pull out specific rows based on cell reference "B7" in the "Documentation" sheet from the "control matrix' sheet:

Sub MoveRowBasedOnCellValue()

    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("Control matrix").UsedRange.Rows.Count
    J = Worksheets("Documentation").UsedRange.Rows.Count
    If J = 1 Then
    If Application.WorksheetFunction.CountA(Worksheets("Documentation").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("Control matrix").Range("A1:S203" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(A).Value) = "B7" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("Documentation").Range("A19" & J + 1)
            J = J + 1
        End If
    Next

Brief description of data acronyms:
Control matrix: Is the sheet where the table is in. I want to pull specific rows from this sheet into the Documentation sheet using the cell reference B7
Documentation: Is the destination path of the rows selected based on cell reference B7.
A1:S203: Is the excel reference of the table under the control matrix sheet
B7: Is the cell reference that consist the details that i want to pull out. This cell reference corresponds the details located in column A in the control matrix sheet
A19: Is the cell that represents the destination where i want the rows to populate into.

I am really new to this and appreciate all the help I can get to get my formula working. I have attached the workbook as reference.

Thanks everyone!!

Regards,

Shankar