Hello dear members,

this is my first forum post ever on the VBA topic, and i am by no stretch skilled when it comes to programming.

I wanted a code that copies and pastes a specific range of cells when certain contitions are met. Here is what i got from https://excelribbon.tips.net/T013399...ext_Value.html

Code:
Sub CopyYes()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet

    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Sheet2")

    J = 1     ' Start copying to row 1 in target sheet
    For Each c In Source.Range("E1:E1000")   ' Do 1000 rows
        If c = "yes" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
End Sub
As i needed to not copy the entire row of the range c i adjusted the code to give back a selection of cells in the row belonging to c by intersecting:

Code:
If c = "yes" Then
Intersect(Rows(c.Row), Range("A1:C5000,F1:H5000,R1:V5000")).Copy Target.Rows(j)
Which works fine, but slow when copying more than 500 ranges.

This is where i got so far, and just to give people with the same problem some code to try out.

The next step i need to make is the following:

I only want those instances of c = "yes" copied, where in the same row of c there is a date in column A that is equal to Month(Now).

I tried with If And like so:
Code:
If c = "yes" And Intersect(Rows(c.Row), Range("A1:A5000")) = Month(Now) Then
this code gave me no errors, but it also didnt copy anything. I tried to put these types of dates into A: MM/DD/YYYY, DD/MM/YYYY, MM/DD/YY, DD/MM/YY

Next i tried this one:

Code:
If c = "yes" And Month(Intersect(Rows(c.Row), Range("A1:A5000"))) = Month(Now) Then
which gives me runtime error 13, but the debugger does only give me the whole line of code as faulty.

If anyone has a solution to this, it would be greatly appreciated!

Special thanks to Doc Alan Elstein for helping me out and suggesting i put up my code on some forum!