Excel VBA: How to copy row from one worksheet to another based on data range with additional criteria to narrow down results?

Question

I have the below code that copies rows from one worksheet to another based on a date range in column B. However, struggling to add a second condition that would eliminate results based on another column (C) containing text "Action". Can anyone help on this?

Private Sub CommandButton3_Click()
    Dim startdate As Date, enddate As Date
    Dim rng As Range, destRow As Long
    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim c As Range

    Set shtSrc = Sheets("Tier2")
    Set shtDest = Sheets("Parameters")

    destRow = 74 

    startdate = DateSerial(Year(Now), Month(Now), 1)
    enddate = DateSerial(Year(Now), Month(Now) + 3, 0)


    Set rng = Application.Intersect(shtSrc.Range("B:B"), shtSrc.UsedRange)


    For Each c In rng.Cells
        If c.Value >= startdate And c.Value <= enddate Then

            c.Offset(0, 1).Resize(1, 10).Copy _
                          shtDest.Cells(destRow, 1)

            destRow = destRow + 1

        End If
    Next

End Sub

Samuel Hulla · Accepted Answer · 2018-06-27 10:42:58Z

1

Use offset to compare the data in column C.
Add the following condition inside your If statement, appended with And

For Each c In rng.Cells
  If c.Value >= startdate And c.Value <= enddate And c.Offset(0, 1) <> "Action" Then
     '....code.... 
  End If
Next c

answered Jun 27, 2018 at 10:42

Samuel Hulla

7,2808 gold badges46 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Samuel Hulla Over a year ago

Nothing to be ashamed about, sometimes the easiest of solutions are the ones that takes us the longest to realize ;)

Collectives™ on Stack Overflow

Excel VBA: How to copy row from one worksheet to another based on data range with additional criteria to narrow down results?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related