VBA Copy a row based upon 2 cells with 3 criteria

[ez08mba]

I'm not sure why this doesn't work on the dummy data?

Problems:

1. It doesn't recognize the September 2011 dates
2. It is supposed to pull dates between July and August (i.e. 2 dates) and pull everything except those dates.

The date strings have to be variable based since it can't be hard coded.

Sub copyrow()
Dim rc As Integer, row As Integer, i As Integer
Dim mm As String, fdt As String, pdt As String, mo As String, yr As String
Dim Date1 As String, Date2 As String
     mm = Month(Date) - 1
     mo = Format(Now(), "mm")  'ex. "08"
     yr = Format(Now(), "yyyy")
     Date1 = mm & "/01/" & yr
     Date2 = mo & "/01/" & yr

     With Worksheets(1).Activate
          rc = Cells(Rows.Count, 1).End(xlUp).row
          For row = rc To 1 Step -1
               If Cells(row, 5).Value = "Decommissioned" Then
                     If Cells(row, 6).Value >= Date1 < Date2 Then
                    
                         Rows(row).EntireRow.Copy
                              Worksheets(2).Activate
                              Range("A1").Select
                              i = Cells(Rows.Count, 1).End(xlUp).row + 1
                              Cells(i, 1).Select
                              ActiveSheet.Paste
                              Worksheets(1).Activate
                    End If
               End If
          Next
     End With

End Sub

[TABLE="width: 740"]
<TBODY>[TR]
[TD]Dept</SPAN>
[/TD]
[TD]Application Id</SPAN>
[/TD]
[TD]Application Name</SPAN>
[/TD]
[TD]Short Name</SPAN>
[/TD]
[TD]State</SPAN>
[/TD]
[TD]Decommission Date</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID11</SPAN>
[/TD]
[TD]App17</SPAN>
[/TD]
[TD]O</SPAN>
[/TD]
[TD]Operate</SPAN>
[/TD]
[TD="align: right"]12/15/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID12</SPAN>
[/TD]
[TD]App18</SPAN>
[/TD]
[TD]P</SPAN>
[/TD]
[TD]Operate</SPAN>
[/TD]
[TD="align: right"]12/15/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID17</SPAN>
[/TD]
[TD]App16</SPAN>
[/TD]
[TD]S</SPAN>
[/TD]
[TD]Retired</SPAN>
[/TD]
[TD="align: right"]7/31/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID6</SPAN>
[/TD]
[TD]App14</SPAN>
[/TD]
[TD]E</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]7/3/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID18</SPAN>
[/TD]
[TD]App15</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]7/3/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID8</SPAN>
[/TD]
[TD]App12</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]6/27/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID5</SPAN>
[/TD]
[TD]App13</SPAN>
[/TD]
[TD]D</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]6/27/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID2</SPAN>
[/TD]
[TD]App11</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]6/19/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID19</SPAN>
[/TD]
[TD]App10</SPAN>
[/TD]
[TD]A</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]5/10/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID16</SPAN>
[/TD]
[TD]App7</SPAN>
[/TD]
[TD]L</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]4/18/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID9</SPAN>
[/TD]
[TD]App8</SPAN>
[/TD]
[TD]M</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]4/18/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID14</SPAN>
[/TD]
[TD]App9</SPAN>
[/TD]
[TD]N</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]4/18/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID4</SPAN>
[/TD]
[TD]App5</SPAN>
[/TD]
[TD]J</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]2/29/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID10</SPAN>
[/TD]
[TD]App6</SPAN>
[/TD]
[TD]K</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]2/29/2012 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID7</SPAN>
[/TD]
[TD]App4</SPAN>
[/TD]
[TD]I</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]12/19/2011 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID13</SPAN>
[/TD]
[TD]App2</SPAN>
[/TD]
[TD]G</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]9/28/2011 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID15</SPAN>
[/TD]
[TD]App3</SPAN>
[/TD]
[TD]H</SPAN>
[/TD]
[TD]Decommissioned</SPAN>
[/TD]
[TD="align: right"]9/28/2011 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID3</SPAN>
[/TD]
[TD]App1</SPAN>
[/TD]
[TD]R</SPAN>
[/TD]
[TD]Retired</SPAN>
[/TD]
[TD="align: right"]9/1/2011 0:00</SPAN>
[/TD]
[/TR]
[TR]
[TD]IT</SPAN>
[/TD]
[TD]ID1</SPAN>
[/TD]
[TD]App19</SPAN>
[/TD]
[TD]Q</SPAN>
[/TD]
[TD]Operate</SPAN>
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Michael M]

Please see your other thread !

 

[VoG]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).