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

[Sintek]

Hi Shankar

Welcome to the Forum.

1. In future please view the forum rules before posting. Your post does not comply with rule #3.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window.

Please correct above by editing post , highlighting code and pressing #

2. Unfortunately screenshots and word doc's are of no use...
Attach a sample workbook . Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

To attach a file, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

[Sintek]

Thank you...
Is this what you want to achieve...

Option Explicit

Sub Shankar()
Dim Process As String
Process = Sheet2.Range("B7")
With Sheet4.Cells(1).CurrentRegion
    .AutoFilter 1, Process
    .SpecialCells(12).Copy Sheet2.Range("A19")
End With
Sheet4.AutoFilterMode = False
End Sub