Hi all
First off just want to say what a great helpful community you have here. I hardly ever post questions mostly because I don't need to due to the help you have given out in the past but this time I am stumped on something. The kind of problem I'm about to describe is something I occasionally run into and can never quite make head nor tails out of so I'm hoping you can help.
I'm building a tool that contains some HR elements and one of those is absence monitoring. Currently I'm writing the code for tidying up all the different sheets when somebody leaves and that includes finding and removing them from three separate absence monitoring locations. The code I'm using for this is below.
As you can see I've kept it almost identical for the three sheets with only small difference to the actual ranges being sorted and the ranges being used to locate the rows to be affected. For each sheet the code heads off to find the row with the leavers' name in it and an empty row from the bottom of the table (these rows contain some formatting which is why I do this). The leavers' row is then cleared of contents and the formatting from the empty row pasted on top. Then the whole sheet is sorted so the newly fresh row is shunted off down the bottom.
This whole process is triggered from a form with a button control that launches the macro in a fourth sheet. When the macro is triggered from this fourth sheet the first section runs fine (the one affecting the Absence Input Sheet) however I get the error from the title when it tries to sort either of the other two (I've swapped them around to make sure that it is both of them it stumbles with and it is). When I run the macro from inside the Visual Basic window it runs fine or if I run it from the button but step into it and manually switch over to the right sheets when it becomes time to sort it runs fine. I am assuming therefore that when the macro is called from the button it is encountering a problem switching to the relevant sheets to do the sort. What I don't understand is why. Originally I had this code in a With Sheets loop but replaced that with specific SHeets references to try and combat this problem with no luck.
Any help would be appreciated on this, I can't work out why it works fine for the Absence Input Sheet but not for the other two when the code is so similar.
First off just want to say what a great helpful community you have here. I hardly ever post questions mostly because I don't need to due to the help you have given out in the past but this time I am stumped on something. The kind of problem I'm about to describe is something I occasionally run into and can never quite make head nor tails out of so I'm hoping you can help.
I'm building a tool that contains some HR elements and one of those is absence monitoring. Currently I'm writing the code for tidying up all the different sheets when somebody leaves and that includes finding and removing them from three separate absence monitoring locations. The code I'm using for this is below.
Code:
lRow = Sheets("Absence Input").Range("C:C").Find(strName, LookIn:=xlValues).Row
lEmptyRow = Sheets("Absence Input").Cells(Rows.Count, 3).End(xlUp).Row + 1
Sheets("Absence Input").Rows(lRow).ClearContents
Sheets("Absence Input").Rows(lEmptyRow).Copy
Sheets("Absence Input").Paste Destination:=Sheets("Absence Input").Cells(lRow, 1)
Sheets("Absence Input").Range("C13:AXK200").Sort key1:=Sheets("Absence Input").Range("C13:C200"), order1:=xlAscending, Header:=xlNo
lRow = Sheets("Staff Entitlements Report").Range("B:B").Find(strName, LookIn:=xlValues).Row
lEmptyRow = Sheets("Staff Entitlements Report").Cells(Rows.Count, 2).End(xlUp).Row + 1
Sheets("Staff Entitlements Report").Rows(lRow).ClearContents
Sheets("Staff Entitlements Report").Rows(lEmptyRow).Copy
Sheets("Staff Entitlements Report").Paste Destination:=Sheets("Staff Entitlements Report").Cells(lRow, 1)
Sheets("Staff Entitlements Report").Range("B6:B200").Sort key1:=Sheets("Staff Entitlements Report").Range("B6:B200"), order1:=xlAscending, Header:=xlNo
lRow = Sheets("Monthly Absence Report").Range("B:B").Find(strName, LookIn:=xlValues).Row
lEmptyRow = Sheets("Monthly Absence Report").Cells(Rows.Count, 2).End(xlUp).Row + 1
Sheets("Monthly Absence Report").Rows(lRow).ClearContents
Sheets("Monthly Absence Report").Rows(lEmptyRow).Copy
Sheets("Monthly Absence Report").Paste Destination:=Sheets("Monthly Absence Report").Cells(lRow, 1)
Sheets("Monthly Absence Report").Range("B7:C200").Sort key1:=Sheets("Monthly Absence Report").Range("B7:B200"), order1:=xlAscending, Header:=xlNoAs you can see I've kept it almost identical for the three sheets with only small difference to the actual ranges being sorted and the ranges being used to locate the rows to be affected. For each sheet the code heads off to find the row with the leavers' name in it and an empty row from the bottom of the table (these rows contain some formatting which is why I do this). The leavers' row is then cleared of contents and the formatting from the empty row pasted on top. Then the whole sheet is sorted so the newly fresh row is shunted off down the bottom.
This whole process is triggered from a form with a button control that launches the macro in a fourth sheet. When the macro is triggered from this fourth sheet the first section runs fine (the one affecting the Absence Input Sheet) however I get the error from the title when it tries to sort either of the other two (I've swapped them around to make sure that it is both of them it stumbles with and it is). When I run the macro from inside the Visual Basic window it runs fine or if I run it from the button but step into it and manually switch over to the right sheets when it becomes time to sort it runs fine. I am assuming therefore that when the macro is called from the button it is encountering a problem switching to the relevant sheets to do the sort. What I don't understand is why. Originally I had this code in a With Sheets loop but replaced that with specific SHeets references to try and combat this problem with no luck.
Any help would be appreciated on this, I can't work out why it works fine for the Absence Input Sheet but not for the other two when the code is so similar.
