locked
getting data for last month using VBA RRS feed

  • Question

  • Sub currentm()
        Dim i As Long, r As Long, l As Long, cool As Integer, mo As Integer, k As Integer, yr As Integer
        cool = Sheets("Sheet2").UsedRange.Columns.Count
        r = WorksheetFunction.CountA(Sheets("Sheet2").Range("B:B"))
        yr = Year(Date)
        mo = Month(Date)
         l = 1
         
        For i = 1 To r
           ' If Month(Cells(i, 5)) = mo - 1 And Year(Cells(i, 5)) = yr Then
           If i = 1 Or (Cells(i, 5) <= Date - Day(Date) And Cells(i, 5) >= DateSerial(Year(Date - Day(Date)), Month(Date - Day(Date)), 1)) Then
                        
                For k = 1 To cool
                     
                    Cells(l, k) = Sheets("Sheet2").Cells(i, k)
                     
                Next k
                l = l + 1
            End If
        Next i
    End Sub
    Tuesday, May 7, 2013 11:11 AM

All replies