Hello All,
I've written code to randomly select 10% of data from a data tab within the workbook I've built the macro to do. However, when I review the data, the results contain dates outside of the Last Month timeframe. I've tried changing the format of the column that contains the dates I'm checking to a Short Date format, but that hasn't work the way I would like. I've included the code in this post. I haven't been able to ffigure out why the results aren't staying within the parameters set up in the VBA code. I've also pasted a screenshot showing wrong dates that shouldn't have been copied over.
This is a screeenshot of my results. If you review the Disposition Date column you can see there are dates that aren't in November. Any help I can get is greatly apprecaiated.
I've written code to randomly select 10% of data from a data tab within the workbook I've built the macro to do. However, when I review the data, the results contain dates outside of the Last Month timeframe. I've tried changing the format of the column that contains the dates I'm checking to a Short Date format, but that hasn't work the way I would like. I've included the code in this post. I haven't been able to ffigure out why the results aren't staying within the parameters set up in the VBA code. I've also pasted a screenshot showing wrong dates that shouldn't have been copied over.
VBA Code:
Sub Filter_by_Last_month()
'
' Filter_by_month Macro
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
Sheets("FILENAME").Range("A:I").AutoFilter Field:=9, Criteria1:=xlFilterLastMonth, _
Operator:=xlFilterDynamic
'Sub CreateSheet()
Application.DisplayAlerts = False
On Error Resume Next
Sheets("Sheet1").Delete
On Error GoTo 0
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Sheet1"
'Sub Copy_Header()
Application.ScreenUpdating = False
Dim h As Long
For h = 2 To Sheets.Count
Sheets("FILENAME").Rows(1).Copy Destination:=Sheets("Sheet1").Rows(1)
Next
Sheets("Sheet1").Cells(1, 1).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
'Sub Copy()
Dim LastRow As Long
Dim NbRows As Long
Dim RowList()
Dim i As Long, J As Long, k As Long
Dim RowNb As Long
Dim s As String
Sheets("FILENAME").Activate
Application.ScreenUpdating = False
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
s = i & ":" & i
If IsEmpty(Cells(i, 1).Value) Then
Rows(s).EntireRow.Hidden = False
End If
Next
Application.ScreenUpdating = True
Sheets("FILENAME").Activate
LastRow = Range("A" & Rows.Count).End(xlUp).Row
NbRows = LastRow * 0.1
ReDim RowList(1 To NbRows)
k = 2
For i = 1 To NbRows
RowNb = Rnd() * LastRow
Rows(RowNb).Copy Destination:=Sheets("Sheet1").Cells(k, "A")
k = k + 1
NextStep:
Next i
End SubThis is a screeenshot of my results. If you review the Disposition Date column you can see there are dates that aren't in November. Any help I can get is greatly apprecaiated.
