Rob_010101
Board Regular
- Joined
- Jul 24, 2017
- Messages
- 198
- Office Version
- 365
- Platform
- Windows
Hello,
The code below is not working properly:
I can't understand how this is happening, as I have another workbook laid out the same with exactly the same code in it and it works perfectly. I'm not an advanced user at all, someone on here kindly put it together for me, so I have no idea how to fix it, or even where to begin.
The only thing I can think of that's different is that I added an additional column H to both the sheets, that's the only difference between this workbook where it's not working and the other one where it is. Could adding an additional column be the cause of it?
Basically, it's moving every single row across to the "previous 12 months" sheet, when it should only be moving rows with an absence start date more than 12 months ago from the current day.
Edit: I've just noticed below where it says J:J. This was the column where the date was which would now be column K:K. Not sure if this helps.
The code below is not working properly:
I can't understand how this is happening, as I have another workbook laid out the same with exactly the same code in it and it works perfectly. I'm not an advanced user at all, someone on here kindly put it together for me, so I have no idea how to fix it, or even where to begin.
The only thing I can think of that's different is that I added an additional column H to both the sheets, that's the only difference between this workbook where it's not working and the other one where it is. Could adding an additional column be the cause of it?
Basically, it's moving every single row across to the "previous 12 months" sheet, when it should only be moving rows with an absence start date more than 12 months ago from the current day.
Edit: I've just noticed below where it says J:J. This was the column where the date was which would now be column K:K. Not sure if this helps.
VBA Code:
Sub Workbook_Open()
' Set variables
' Move date
Dim ToBeMovedDate As Long
ToBeMovedDate = DateSerial(Year(Date) - 1, Month(Date), Day(Date))
' Delete date
Dim ToBeDeletedDate As Long
ToBeDeletedDate = DateSerial(Year(Date) - 2, Month(Date), Day(Date))
' Last row with data, current 12 months
Dim LastRowCurrent12Months As String
LastRowCurrent12Months = Sheets("Current 12 months").Cells(Sheets("Current 12 months").Rows.Count, "A").End(xlUp).Row
' Last row with data, previous 12 months
Dim LastRowPrevious12Months As String
LastRowPrevious12Months = Sheets("Previous 12 months").Cells(Sheets("Previous 12 months").Rows.Count, "A").End(xlUp).Row
' Remove existing filters , if applicable
On Error Resume Next
Sheets("Current 12 months").ShowAllData
Sheets("Previous 12 months").ShowAllData
On Error GoTo 0
' o-------------------------------------------------------------o
' | MOVE LINES FROM CURRENT 12 MONTHS TO PREVIOUS 12 MONTHS |
' o-------------------------------------------------------------o
' Verify there are rows to be moved
NumberOfRowsOnCurrent = WorksheetFunction.CountIfs(Sheets("Current 12 months").Range("J:J"), "<=" & ToBeMovedDate)
If NumberOfRowsOnCurrent > 0 Then
' Filter dates to be moved
Sheets("Current 12 months").Range("A:N").AutoFilter Field:=10, Criteria1:="<=" & ToBeMovedDate
' Copy data to be moved to previous 12 months
Sheets("Current 12 months").Select
Sheets("Current 12 months").Range("A2:N" & LastRowCurrent12Months).Select
Selection.Offset(0, 0).SpecialCells(xlCellTypeVisible).EntireRow.Copy
' Paste data into previous 12 months worksheet
Sheets("Previous 12 months").Select
Sheets("Previous 12 months").Range("A" & LastRowPrevious12Months + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' Remove copied data from current 12 months
Sheets("Current 12 months").Select
Sheets("Current 12 months").Range("A2:N" & LastRowCurrent12Months).Select
Selection.Offset(0, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
' Remove filter
Sheets("Current 12 months").ShowAllData
End If
' o-------------------------------------------------------------o
' | REMOVE LINES FROM PREVIOUS 12 MONTHS |
' o-------------------------------------------------------------o
' Verify there are rows to be removed
NumberOfRowsOnPrevious = WorksheetFunction.CountIfs(Sheets("Previous 12 months").Range("J:J"), "<=" & ToBeDeletedDate)
If NumberOfRowsOnPrevious > 0 Then
' Filter dates to be deleted
Sheets("Previous 12 months").Select
Sheets("Previous 12 months").Range("A:N").AutoFilter Field:=10, Criteria1:="<=" & ToBeDeletedDate
' Select rows to be removed
Sheets("Previous 12 months").Range("A2:N" & LastRowPrevious12Months).Select
' Delete rows
Selection.Offset(0, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
' Remove filter
Sheets("PRevious 12 months").ShowAllData
End If
End Sub
Last edited: