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

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

<TBODY>
</TBODY>

 

[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).