Copy/paste rows when text is found

[Trudzia84]

I have a spreadsheet where in column A are peoples names, in column B 3digit codes and in column 3 dates.
In cell B1 is choosen code and in cell C1 is choosen month/year. I want to write a macro which will search for a code from cell B1 within the whole sheet and paste all rows with this code to the new sheet. I have managed to this with this code:

Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet

Set Source = ActiveWorkbook.Worksheets("Sheet1")
Set Target = ActiveWorkbook.Worksheets("Sheet2")

j = 2
For Each c In Source.Range("B1:B30000")
If c = Range("$B$1").Value Then
       Source.Rows(c.Row).Copy Target.Rows(j)
       j = j + 1
    End If
Next c
End Sub

How can I adjust this code so it will not only search for all people with the code mentioned in cell B1 but will only return the rows where there is both the correct B1 code and the date in column C is within the month mentioned in cell C1? In the example below I would only like to get a line "Olaf 307 20/01/2021" as a result.

 

[Wookiee]

I created a function to determine whether the date is within a specified month. Please try using this code:

Function IsDateInMonth _
  (d As Date, m As Date) As Boolean
'Crafted by Wookiee at MrExcel.com


Dim dStart As Date
Dim dEnd As Date

dStart = CDate(VBA.Month(m) & "/1/" & VBA.Year(m))
dEnd = WorksheetFunction.EoMonth(m, 0)

If d >= dStart And d <= dEnd Then IsDateInMonth = True

End Function

Sub Trudzia()

Dim strCode As String
Dim datDate As Date
Dim rngCell As Range
Dim lngLoop As Long
Dim wksSource As Worksheet
Dim wksTarget As Worksheet

Set wksSource = ActiveWorkbook.Worksheets("Sheet1")
Set wksTarget = ActiveWorkbook.Worksheets("Sheet2")

lngLoop = 2

strCode = Range("B1")
datDate = Range("C1")

For Each rngCell In wksSource.Range("B4:B30000")

  If rngCell = strCode _
  And IsDateInMonth(rngCell.Offset(, 1), datDate) Then

    wksSource.Rows(rngCell.Row).Copy wksTarget.Rows(lngLoop)
    lngLoop = lngLoop + 1

  End If

Next rngCell

End Sub

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Trudzia84]

I created a function to determine whether the date is within a specified month. Please try using this code:

Function IsDateInMonth _
  (d As Date, m As Date) As Boolean
'Crafted by Wookiee at MrExcel.com


Dim dStart As Date
Dim dEnd As Date

dStart = CDate(VBA.Month(m) & "/1/" & VBA.Year(m))
dEnd = WorksheetFunction.EoMonth(m, 0)

If d >= dStart And d <= dEnd Then IsDateInMonth = True

End Function

Sub Trudzia()

Dim strCode As String
Dim datDate As Date
Dim rngCell As Range
Dim lngLoop As Long
Dim wksSource As Worksheet
Dim wksTarget As Worksheet

Set wksSource = ActiveWorkbook.Worksheets("Sheet1")
Set wksTarget = ActiveWorkbook.Worksheets("Sheet2")

lngLoop = 2

strCode = Range("B1")
datDate = Range("C1")

For Each rngCell In wksSource.Range("B4:B30000")

  If rngCell = strCode _
  And IsDateInMonth(rngCell.Offset(, 1), datDate) Then

    wksSource.Rows(rngCell.Row).Copy wksTarget.Rows(lngLoop)
    lngLoop = lngLoop + 1

  End If

Next rngCell

End Sub

Wow. It is almost perfect. The only issue I noticed is that it works great for dates in January but if I write any other month in "C1" the macro copies all rows from January to the given month, not only the rows from this particular month.

 

[Wookiee]

Wow. It is almost perfect. The only issue I noticed is that it works great for dates in January but if I write any other month in "C1" the macro copies all rows from January to the given month, not only the rows from this particular month.

I suspect that it has to do with how you are entering your dates in C1. My code works on the date value in C1, so in my testing, I typed a full date into that cell.

I did see that you had your C1 cell displayed as 01/2021, but I entered 1/1/21 in that cell on the test workbook I used. The date in that cell doesn't have to be the 1st of the month you want to evaluate, but it's easiest to simply do so. So enter 2/1/21 for February 2021, 3/1/21 for March 2021, etc.

Hope that helps!

 

[Trudzia84]

I suspect that it has to do with how you are entering your dates in C1. My code works on the date value in C1, so in my testing, I typed a full date into that cell.

I did see that you had your C1 cell displayed as 01/2021, but I entered 1/1/21 in that cell on the test workbook I used. The date in that cell doesn't have to be the 1st of the month you want to evaluate, but it's easiest to simply do so. So enter 2/1/21 for February 2021, 3/1/21 for March 2021, etc.

Hope that helps!

Thanks. Now I know what is wrong. My Excel dates settings are different than yours (dd/mm/yyyy) and that's why the macro works differently when I use it

 

[Trudzia84]

I have been asked to leave the dates in column C in the format they are at the moment (so dd/mm/yyyy) and in cell C1 mm/yyyy. Do you know how I should adjust the above code so it works correctly and return only the results from the month given in C1?

 

[Wookiee]

I have been asked to leave the dates in column C in the format they are at the moment (so dd/mm/yyyy) and in cell C1 mm/yyyy. Do you know how I should adjust the above code so it works correctly and return only the results from the month given in C1?

I would suggest formatting the cell so that it displays in MM/YYYY format, but still entering the full date (in your native format) into the cell. That way, it still displays what you want to see and it can still be used by the macro to determine whether the date is within a certain month. But the function requires a specific date to calculate; it won't work on a simple month/year combination.