Be seen. Boost your question’s priority for more expert views and faster solutions
Create an automated Employee Database in Excel
I've created an employee database in excel for the purpose of importing and linking the document to a Visio Org. Chart.
The first worksheet is a MASTER and contains all employee info, all other worksheets and the respective cells are linked to the MASTER sheet.
I'm looking for greater automation when it come to deleting or adding an employee to the MASTER sheet. Currently, when an employee is deleted a "#REF!" appears everywhere that employee was linked. When an employee is added you have to insert a row on the appropriate worksheet and paint down the value for the added row (employee).
I would like these changes to occur automaticially, or with the aid of a macro, I would like to keep the database outside of Access.
The first worksheet is a MASTER and contains all employee info, all other worksheets and the respective cells are linked to the MASTER sheet.
I'm looking for greater automation when it come to deleting or adding an employee to the MASTER sheet. Currently, when an employee is deleted a "#REF!" appears everywhere that employee was linked. When an employee is added you have to insert a row on the appropriate worksheet and paint down the value for the added row (employee).
I would like these changes to occur automaticially, or with the aid of a macro, I would like to keep the database outside of Access.
Asked:
Who is Participating?
You could also upload a simple sample of the file, that is, with just half a dozen or so fictional employees and how they relate to the other sheet.
Here you go.
EDforEE.XLSX
EDforEE.XLSX
That is really complicated. I don't think I can understand that without an explanation of each sheet.
Anyway, it seems to me that you will need VBA to automate it. You can't do it with Excel, cause you're populating automatically from the first sheet. You actually add data to other sheets.
Anyway, it seems to me that you will need VBA to automate it. You can't do it with Excel, cause you're populating automatically from the first sheet. You actually add data to other sheets.
"OCW260" is the data driver.
"Totals" is irrelevant.
"EO, OR&S, BO, EMO, MSF, FOF, AMF, BPFTI" are the various depts. and the specific personnel in them.
"EO Vacant, OR&S Vacant, BO Vacant.... etc" are the vacancies in each dept.
"AC Funded" is irrelevant
"IPA" is irrelevant
I suppose i could just create a macro that deletes all rows that contain "#REF!" when you press Ctrl+X, but I was really looking for a better way (especially for the if creating an employee situation).
"Totals" is irrelevant.
"EO, OR&S, BO, EMO, MSF, FOF, AMF, BPFTI" are the various depts. and the specific personnel in them.
"EO Vacant, OR&S Vacant, BO Vacant.... etc" are the vacancies in each dept.
"AC Funded" is irrelevant
"IPA" is irrelevant
I suppose i could just create a macro that deletes all rows that contain "#REF!" when you press Ctrl+X, but I was really looking for a better way (especially for the if creating an employee situation).
You could use Worksheet_Change even to monitor any changes, including adding an employee.
The biggest difficulty, from what I gather, is that, when you hire a new employee, you manually add it to the respective department, then paint down the rest of the cells. There really isn't an automatic check that tries to match a new line in OCW260 with the department.
So, VBA would be your biggest friend. Seeing as the things you're trying to do are mostly simple, you can probably do it on your own. If you have any difficulty, use the macro recorder to replicate an action and see how VBA does it.
The biggest difficulty, from what I gather, is that, when you hire a new employee, you manually add it to the respective department, then paint down the rest of the cells. There really isn't an automatic check that tries to match a new line in OCW260 with the department.
So, VBA would be your biggest friend. Seeing as the things you're trying to do are mostly simple, you can probably do it on your own. If you have any difficulty, use the macro recorder to replicate an action and see how VBA does it.
Polak,
A couple of questions;
1) Can an employee be on more than one sheet?
2) Which column on the master sheet will decided which individual sheet(s) the employee would appear on?
If the adding to a sheet can not be logically derived from the master sheet, you would need to add a mechanism for selecting a target sheet(s), say a pop up list on an Excel form.
This is not a straightforward task, not impossible for the experts however
A couple of questions;
1) Can an employee be on more than one sheet?
2) Which column on the master sheet will decided which individual sheet(s) the employee would appear on?
If the adding to a sheet can not be logically derived from the master sheet, you would need to add a mechanism for selecting a target sheet(s), say a pop up list on an Excel form.
This is not a straightforward task, not impossible for the experts however
-Polak,
That is hugely complex and would require a lot of detailed work to track down every cell reference for a specific employee. I believe that due to the complexity it would be best for you to record a macro and then see whether that can be made generic.
Patrick
That is hugely complex and would require a lot of detailed work to track down every cell reference for a specific employee. I believe that due to the complexity it would be best for you to record a macro and then see whether that can be made generic.
Patrick
It probably can. All he needs to do is:
If the event is a deleted row, hunt down any #REFs and delete that row. If it's possible (not really sure about this), read the department and store the appropriate sheets in a variable, so all you need to do is select the correct sheet and delete the appropriate records.
If the event is a new row, then store the appropriate sheets in a variable, and then do the same steps for it. (I've noticed that each sheet is similar to the other departments).
If the event is a deleted row, hunt down any #REFs and delete that row. If it's possible (not really sure about this), read the department and store the appropriate sheets in a variable, so all you need to do is select the correct sheet and delete the appropriate records.
If the event is a new row, then store the appropriate sheets in a variable, and then do the same steps for it. (I've noticed that each sheet is similar to the other departments).
Cluskitt you lost me at VBA.
Runrigger:
1) No; however, when someone clears a BI or EODs it is necessary to manually move him from a "deptx Vacant" sheet to the regular "deptx" sheet.... (this is less of a big deal)
2) Two collums technically dictate that; the "Department" (F) to indicates which department sheet they should be on, and "Work Location" indicates if they are a vacancy.
Runrigger:
1) No; however, when someone clears a BI or EODs it is necessary to manually move him from a "deptx Vacant" sheet to the regular "deptx" sheet.... (this is less of a big deal)
2) Two collums technically dictate that; the "Department" (F) to indicates which department sheet they should be on, and "Work Location" indicates if they are a vacancy.
In VBA, there is an event called Worksheet_Change. Every time a cell is changed, that event is called. You can then run any kind of code. In this case, and seeing as there are only 2 cases that interest you, you can add a check to see whether a row was deleted or inserted. Depending on which it was, you can run a set of actions and it would be done every time a new row was deleted or added.
If you have difficulty coding it, you can use the macro recorder, then simulate those situations:
-Do all the actions you would do if a row was deleted and check the code, so you can adapt it to your needs.
-Do the same for a new row.
If you need help with that, you can post that automated code, and we can adapt it to your needs.
If you have difficulty coding it, you can use the macro recorder, then simulate those situations:
-Do all the actions you would do if a row was deleted and check the code, so you can adapt it to your needs.
-Do the same for a new row.
If you need help with that, you can post that automated code, and we can adapt it to your needs.
To preface, I've never even opened VBA, but am willing to give it a shot... unless anyone has a better suggestion? (runrigger?)
Macro recorder is in the toolbar. Open it, set it to record, give it a name and then do all the actions you normally do when inserting an employee. When you're done, go to the same place and stop recording. Then start recording again, give it another name and do all the actions you normally do when deleting an employee.
After that, hit ALT+F11. In the left panel, you'll see your workbook and all the sheets. At the end, there should be a "+ Modules". Expand it, double click on the Module1 that is there. You can now see all the code you created. Copy it and paste it here. We'll have a look and assist you in getting things to work.
One advice, though: create a backup copy of the file, and from now on work only on that copy until you're sure everything is working 100%.
After that, hit ALT+F11. In the left panel, you'll see your workbook and all the sheets. At the end, there should be a "+ Modules". Expand it, double click on the Module1 that is there. You can now see all the code you created. Copy it and paste it here. We'll have a look and assist you in getting things to work.
One advice, though: create a backup copy of the file, and from now on work only on that copy until you're sure everything is working 100%.
Delete an Employee:
Sub DeleteEmployee()
'
' DeleteEmployee Macro
' Delete "#REF!" Linked Rows
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Selection.Find(What:="#REF
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activ
Sheets("EO").Select
Cells.Find(What:="#REF", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activ
Rows("2:2").Select
Selection.Delete Shift:=xlUp
End Sub
Sub DeleteEmployee()
'
' DeleteEmployee Macro
' Delete "#REF!" Linked Rows
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Selection.Find(What:="#REF
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activ
Sheets("EO").Select
Cells.Find(What:="#REF", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activ
Rows("2:2").Select
Selection.Delete Shift:=xlUp
End Sub
Create and Employee:
Sub CreateEmployee()
'
' CreateEmployee Macro
' Run Macro when creating an employee
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLe
ActiveCell.FormulaR1C1 = "John Smith"
Rows("8:8").Select
Range("B8").Activate
ActiveCell.FormulaR1C1 = "Jane Smith"
Rows("8:8").Select
Range("C8").Activate
ActiveCell.FormulaR1C1 = "Program Manager"
Rows("8:8").Select
Range("D8").Activate
ActiveCell.FormulaR1C1 = "GS-340-14"
Rows("8:8").Select
Range("E8").Activate
ActiveCell.FormulaR1C1 = "15"
Rows("8:8").Select
Range("F8").Activate
ActiveCell.FormulaR1C1 = "Executive Office"
Range("H8").Select
ActiveCell.FormulaR1C1 = "DC"
Range("I8").Select
ActiveCell.FormulaR1C1 = "3556"
Range("J8").Select
ActiveCell.FormulaR1C1 = "8- Non Supervisory"
Range("K7").Select
Selection.Copy
Range("K8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("EO").Select
Rows("7:7").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLe
Range("A6").Select
Selection.AutoFill Destination:=Range("A6:A7"
Range("A6:A7").Select
Selection.AutoFill Destination:=Range("A6:K7"
Range("A6:K7").Select
Range("A7").Select
End Sub
Sub CreateEmployee()
'
' CreateEmployee Macro
' Run Macro when creating an employee
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLe
ActiveCell.FormulaR1C1 = "John Smith"
Rows("8:8").Select
Range("B8").Activate
ActiveCell.FormulaR1C1 = "Jane Smith"
Rows("8:8").Select
Range("C8").Activate
ActiveCell.FormulaR1C1 = "Program Manager"
Rows("8:8").Select
Range("D8").Activate
ActiveCell.FormulaR1C1 = "GS-340-14"
Rows("8:8").Select
Range("E8").Activate
ActiveCell.FormulaR1C1 = "15"
Rows("8:8").Select
Range("F8").Activate
ActiveCell.FormulaR1C1 = "Executive Office"
Range("H8").Select
ActiveCell.FormulaR1C1 = "DC"
Range("I8").Select
ActiveCell.FormulaR1C1 = "3556"
Range("J8").Select
ActiveCell.FormulaR1C1 = "8- Non Supervisory"
Range("K7").Select
Selection.Copy
Range("K8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("EO").Select
Rows("7:7").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLe
Range("A6").Select
Selection.AutoFill Destination:=Range("A6:A7"
Range("A6:A7").Select
Selection.AutoFill Destination:=Range("A6:K7"
Range("A6:K7").Select
Range("A7").Select
End Sub
Sorry I haven't got back to you yet. I haven't had much time. But from what I see, with the sample you've shown me and the sample file, it should be pretty easy to code something :)
Take your time, (i think) the only difficult step will be creating several If-Then statements which point which worksheet an employee is on based on their Department.
That shouldn't be that complicated, really. It would be more complicated to wait for enough changes to have all data to fill in, but a simple count would do that. It should be pretty simple, and other experts could probably work it out as well. I will probably only have time to start working on it on wednesday.
The problem with creating it in Access is that there is a Visio Org. Chart currently linked to the excel sheet. I realize that you can link Visio to an Access DB; however, for ease of administration to the users of the database (becuase not everyone is familiar with Access) it was decieded to keep it in Excel.
You don't really need to link Visio with Access. You can still link Visio with Excel, you just link Excel with Access first. That is, you create queries in the same place where your info is now. For example, you could, on the first sheet, have: select * from employees
Then, on the third sheet: select * from employees where department="EO"
etc...
Once you fire/hire someone, all you need to do is refresh the corresponding queries and all data is updated and shifted.
Then, on the third sheet: select * from employees where department="EO"
etc...
Once you fire/hire someone, all you need to do is refresh the corresponding queries and all data is updated and shifted.
LOL. Sure, I can help you with that, if you want. You should open another question for the access "migration", but if you want to keep asking here, it's fine by me.
Looking at your Macro's right now.
There seems to be a problem with the InsertEmployee:
If you insert the employee at the end of a "Departments" group of employees, it works fine. However, if you insert the employee in the middle of the pack then when you run the macro you get lots of "0"s for the reference. Since, employees are added in order of who they report to is there a way around this?
There seems to be a problem with the InsertEmployee:
If you insert the employee at the end of a "Departments" group of employees, it works fine. However, if you insert the employee in the middle of the pack then when you run the macro you get lots of "0"s for the reference. Since, employees are added in order of who they report to is there a way around this?
Probably. I'd have to look into it. I just autofilled from the last line. But, as long as you don't add it at the top, it should be pretty simple to autofill from the first row to the last. I'll look into it as soon as I can (meaning, as soon as I can figure out how to construct this monstrous query I'm building :P).
Yes, I knew that if you added an employee in the middle that it wouldn't work. And yes, there is an easy workaround, but I thought the other might do.
Anyway, the fix is easy, now that I looked at it. Just replace the last line with:
Range("A2").EntireRow.Auto
Anyway, the fix is easy, now that I looked at it. Just replace the last line with:
Range("A2").EntireRow.Auto
Works just fine! However it paints the spaces inbetween certain employees but those blank rows will be deleted once I have a complete database.
Thanks for the nifty Macros!
Thanks for the nifty Macros!
Yes, the way I fixed it is, when you add a new employee, it basically selects the second row and paints all the way to the bottom (that is, the department employees+1, which is the new hire).
Mind you, this is just one way of doing it. You could do it with excel formulas as well. Something like:
=IF('OCW260'!F2="Executive
Basically, replace the formulas you have with these. Remember to respect the first number, though, as that will be the starting row of the department, as you most likely well know :)
Mind you, this is just one way of doing it. You could do it with excel formulas as well. Something like:
=IF('OCW260'!F2="Executive
Basically, replace the formulas you have with these. Remember to respect the first number, though, as that will be the starting row of the department, as you most likely well know :)
Is there a way to insert a rule into the InsertEmployee Macro, that basically says IF "WorkLocation" is equal to anything that has the word "Vacant" in it then do not paint?
Actuallly I've notice another small issue with the Delete Macro.
I've attached a new excel file for your review (in case i screwed somethign up, had to modify the code a bit to account for a new collumn "IPN") but don't think that explains whats happening.
Basically, when you insert an employee and run the ctrl+shift+I macro (I go ahead and delete the vacancy it copies over, see my previous comment) Then if I run the ctrl+shift+D macro to delete that new employee it also deletes the last line on the corresponding Departments sheet.
Why is that?
EDforEE2.xlsm
I've attached a new excel file for your review (in case i screwed somethign up, had to modify the code a bit to account for a new collumn "IPN") but don't think that explains whats happening.
Basically, when you insert an employee and run the ctrl+shift+I macro (I go ahead and delete the vacancy it copies over, see my previous comment) Then if I run the ctrl+shift+D macro to delete that new employee it also deletes the last line on the corresponding Departments sheet.
Why is that?
EDforEE2.xlsm
For the worklocation part, I'm not sure I understand what you want. If the worklocation is vacant, isn't it easier not to run the macro?
Anyway, if you do want to add the check (possibly in case you don't notice it's vacant), just add:
Sub InsertEmployee()
If Left(Cells(ActiveCell.Row,
Dim strSheet As String
...
...
End If
End Sub
As for the line, yes, that is a consequence of painting on insert and deleting row on delete. To fix it, you can add this to the delete macro:
Cells(iRow2, 1).EntireRow.Delete shift:=xlUp
Cells(Range("B1").End(xlDo
End Sub
Anyway, if you do want to add the check (possibly in case you don't notice it's vacant), just add:
Sub InsertEmployee()
If Left(Cells(ActiveCell.Row,
Dim strSheet As String
...
...
End If
End Sub
As for the line, yes, that is a consequence of painting on insert and deleting row on delete. To fix it, you can add this to the delete macro:
Cells(iRow2, 1).EntireRow.Delete shift:=xlUp
Cells(Range("B1").End(xlDo
End Sub
I'm saying that while the macro is painting an entire Department, could it basically not paint the row that has an instance of the word vacant in the WorkLocation column, that way I don't have to go back and delete the vacancies from the individual worksheets as they belong on the "Xdepts Vacant" worksheet.
Ah, I see. that is more complicated. I guess it could paint them all, then delete the vacants. Something like:
Sub InsertEmployee()
Dim strSheet As String
Select Case Cells(ActiveCell.Row, 7).Value
Case "Executive Director FM&E"
strSheet = "EO"
Case "Executive Office"
strSheet = "EO"
Case "Organizational Resources & Support"
strSheet = "OR&S"
Case "Budget Office"
strSheet = "BO"
Case "Enterprise Management Office"
strSheet = "EMO"
Case "Mission Support Facilities"
strSheet = "MSF"
Case "Air & Marine Facilities"
strSheet = "AMF"
Case "Office of Border Patrol Facilities"
strSheet = "BPFTI"
Case "Office of Facilities Operations"
strSheet = "FOF"
End Select
intDepRows = Cells.Find(What:=Cells(Act
Cells.Find(What:=Cells(Act
Sheets(strSheet).Activate
MaxRow = intDepRows + 1
Rows("3:" & Range("B65336").End(xlUp).
Range("A2").EntireRow.Auto
For i = 2 To MaxRow
If i > MaxRow Then
Exit For
ElseIf Left(Cells(i, 9).Value, 6) = "Vacant" Then
Cells(i, 10).EntireRow.Delete
i = i - 1
MaxRow = MaxRow - 1
End If
Next
Cells(MaxRow + 3, 2).Value = "Total:"
Cells(MaxRow + 3, 3).Formula = "=COUNTA(B2:B" & MaxRow & ")"
Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Interior.ColorIndex = 37
Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Borders(xlEdgeBottom).L
Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Borders(xlEdgeLeft).Lin
Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Borders(xlEdgeRight).Li
Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Borders(xlEdgeTop).Line
End Sub
What I did was:
1-Count the number of employees in the department (find the last row, find the first row, subtract them). This is so we know exactly how many rows we need to paint.
2-Delete everything from row 3 to the end (including the total)
3-Paint from row 2 to the last employee
4-Delete all vacants
5-Create Total 3 rows below the last employee
If you need to change the number of rows for the total, just change MaxRow+3 to MaxRow+5 or whatever. If you want a variable number of rows, you can "Dim intTotalRow As Integer" after the strSheet dim, then assign a value to it per department on the select case (same place you assign strSheet), then change MaxRow+3 to MaxRow+intTotalRow.
Anyway, hope this covers what you want. I had to make sure I delete the total as well, else it could be painted over on departments with lots of vacants, and it could be duplicated in departments with no vacants.
Sub InsertEmployee()
Dim strSheet As String
Select Case Cells(ActiveCell.Row, 7).Value
Case "Executive Director FM&E"
strSheet = "EO"
Case "Executive Office"
strSheet = "EO"
Case "Organizational Resources & Support"
strSheet = "OR&S"
Case "Budget Office"
strSheet = "BO"
Case "Enterprise Management Office"
strSheet = "EMO"
Case "Mission Support Facilities"
strSheet = "MSF"
Case "Air & Marine Facilities"
strSheet = "AMF"
Case "Office of Border Patrol Facilities"
strSheet = "BPFTI"
Case "Office of Facilities Operations"
strSheet = "FOF"
End Select
intDepRows = Cells.Find(What:=Cells(Act
Cells.Find(What:=Cells(Act
Sheets(strSheet).Activate
MaxRow = intDepRows + 1
Rows("3:" & Range("B65336").End(xlUp).
Range("A2").EntireRow.Auto
For i = 2 To MaxRow
If i > MaxRow Then
Exit For
ElseIf Left(Cells(i, 9).Value, 6) = "Vacant" Then
Cells(i, 10).EntireRow.Delete
i = i - 1
MaxRow = MaxRow - 1
End If
Next
Cells(MaxRow + 3, 2).Value = "Total:"
Cells(MaxRow + 3, 3).Formula = "=COUNTA(B2:B" & MaxRow & ")"
Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Interior.ColorIndex = 37
Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Borders(xlEdgeBottom).L
Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Borders(xlEdgeLeft).Lin
Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Borders(xlEdgeRight).Li
Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Borders(xlEdgeTop).Line
End Sub
What I did was:
1-Count the number of employees in the department (find the last row, find the first row, subtract them). This is so we know exactly how many rows we need to paint.
2-Delete everything from row 3 to the end (including the total)
3-Paint from row 2 to the last employee
4-Delete all vacants
5-Create Total 3 rows below the last employee
If you need to change the number of rows for the total, just change MaxRow+3 to MaxRow+5 or whatever. If you want a variable number of rows, you can "Dim intTotalRow As Integer" after the strSheet dim, then assign a value to it per department on the select case (same place you assign strSheet), then change MaxRow+3 to MaxRow+intTotalRow.
Anyway, hope this covers what you want. I had to make sure I delete the total as well, else it could be painted over on departments with lots of vacants, and it could be duplicated in departments with no vacants.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month10 days, 21 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
Sub InsertEmployee()
Dim strSheet As String
Select Case Cells(ActiveCell.Row, 6).Value
Case "Executive Office"
strSheet = "EO"
Case "Another Office" 'just add all cases here. You replaced the values, so I can't add them all myself
strSheet = "AO"
End Select
Sheets(strSheet).Activate
Range("A1").End(xlDown).En
End Sub
Sub DeleteEmployee()
Dim strSheet, strName As String
Dim iRow1, iRow2 As Integer
iRow1 = ActiveCell.Row
Select Case Cells(iRow1, 6).Value
Case "Executive Office"
strSheet = "EO"
Case "Another Office" 'just add all cases here. You replaced the values, so I can't add them all myself
strSheet = "AO"
End Select
strName = Cells(iRow1, 1).Value
Sheets(strSheet).Activate
Range("A2:A" & Range("A1").End(xlDown).Ro
Selection.Find(What:=strNa
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activ
iRow2 = ActiveCell.Row
Sheets("OCW260").Activate
Cells(iRow1, 1).EntireRow.Delete
Sheets(strSheet).Activate
Cells(iRow2, 1).EntireRow.Delete shift:=xlUp
End Sub
In the end, however, you should consider changing the format you have now. It's very impractical. At the very least, you should have a few IFs, so you don't really need to be adding autofills.
Would be much easier to simply move it all to access, though. Then you could fill in the data by queries. Any new or removed employee, and all you needed to do was refresh.