VBA code to copy range of cells in row based on criteria

[ryanw1]

Good Evening,

I have been searching for some time now for examples online and similar post for what I need with no success. I am here now to ask for assistance.

I need a VBA code that will be run by a command button to copy a specific range of cells in a specific row and paste the cells to a different sheet. This would only be based on if any cells in column D contains "ECS" and are not hidden. Example: D12, D15, D17 are not hidden and contain "ECS". Cell ranges B12:I12, B15:I15, and B17:I17 are all copied and pasted to sheet 2 starting at Cell B2.

All help will be greatly appreciated.

 

[ryanw1]

Yes, if you will answer questions 1 & 2 from post #8 .

The order form sheet is names ECS Testing Scope.

The range to be cleared is B8 - F30

Thanks for you help.

 

[JoeMo]

The order form sheet is names ECS Testing Scope.

The range to be cleared is B8 - F30

Thanks for you help.

You'll have to integrate this with your code for different source sheets or post that code and ask for some help. The code below asssumes the source sheet is active when the code is executed.

Sub CopyIfECS2()
Dim R As Range, c As Range, NxRw As Long, OrderSht As Worksheet
Set OrderSht = Sheets("ECS Testing Scope")
OrderSht.Range("B8:F30").ClearContents
Set R = Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
For Each c In R
    If c.Value = "ECS" Then
        If c.EntireRow.Hidden = False Then
            NxRw = OrderSht.Cells(OrderSht.Rows.Count, "B").End(xlUp).Row + 1
            Range(Cells(c.Row, "B"), Cells(c.Row, "I")).Copy Destination:=OrderSht.Range("B" & NxRw)
        End If
    End If
Next c
End Sub