I m trying to write a code that will look in worksheet "Sox Controls 2018" for a value "Nov1" in Column C (The heading in column starts in C3) and copy over data that's in columns D, F, H, J, P, Q, R, I to a new worksheet that is called "Nov" starting in cell A2.
Here is what have so far which copies the whole row but I only want the rows that I mentioned above. And I want the data to start in A2 in "Nov" worksheet.
Thanks so much!
Here is what have so far which copies the whole row but I only want the rows that I mentioned above. And I want the data to start in A2 in "Nov" worksheet.
Code:
Sub November2()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("SOX Controls 2018").UsedRange.Rows.Count
J = Worksheets("Nov").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Nov").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("SOX Controls 2018").Range("C3:C" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "Nov1" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Nov").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
Thanks so much!