VB Code Help!!

[Chris_010101]

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.

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

 

[Trebor76]

Hi Chris,

Yes the reference to column J now needs to be K.

In addition change any reference to N to O and this...

Field:=10

...to this:

Field:=11

Hope that helps.

Robert

 

[Chris_010101]

Hi Chris,

Yes the reference to column J now needs to be K.

In addition change any reference to N to O and this...

Field:=10

...to this:

Field:=11

Hope that helps.

Robert

This report is going to my CEO on Monday and you've just potentially saved my behind (and also me working on this all weekend)...

I've been working on this loading all the data on since I got home from work at 7pm, it's now 01:20am (in the UK) and then that happened with the code.

Cannot thank you enough!!

THANK YOU!

 

[Trebor76]

Thanks for letting us know and you're welcome. Hope it all goes well and get some rest