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.
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.