HELP. VBA Cell Reference Issue

[imback2nite]

The problem that I’m having is when I change the sheet name. For example Employee 5 to Top Cat and run the below code, I get a reference error.


=IF('Employee 5'!J14="","",VLOOKUP('Employee 5'!J14,$A$3:$B$26,2,FALSE))
What I need to do is to be able to change the Sheet name, gather the information and change the sheet name again.....etc.
I use VB to speed up the process.

Sub CommandButton1_Click()
Unload UserForm7
Sheets("Payroll").Select
Application.ScreenUpdating = False
Sheets("Worksheet").Visible = True
Sheets("Worksheet").Select
Range("D5:AF57").Select
Selection.Replace What:=Range("AH9"), Replacement:=Range("AH5")
Range("D1:Q2").Select
Selection.Copy
Sheets("Payroll").Select
Range("D12").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Worksheet").Select
Range("D5:AF57").Select
Selection.Replace What:=Range("AH5"), Replacement:=Range("AH9")
Range("D1:Q2").Select
Selection.Copy
Sheets("Payroll").Select
Range("D28").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Worksheet").Visible = False
Range("B1").Select
Application.ScreenUpdating = True
Unload UserForm2
ActiveSheet.Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
True, AllowFormattingCells:=False, AllowFormattingColumns:=False, _
AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows _
:=False, AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, _
AllowDeletingRows:=False, AllowSorting:=False, AllowFiltering:=False, _
AllowUsingPivotTables:=False, Password:="***"
ActiveSheet.EnableSelection = xlNoRestrictions
MsgBox "Updating Payroll Complete."
End Sub

Cells AH5 and AH9 contain the Employee names.
What happens is I get a #NAME? Error. I look at the formula and it is now from this...
=IF('Employee 5'!J14="","",VLOOKUP('Employee 5'!J14,$A$3:$B$26,2,FALSE))
to this
=IF(Top ‘Cat’!J14="","",VLOOKUP(Top ‘Cat’!J14,$A$3:$B$26,2,FALSE))

The cell reference is now 'Cat' instead of ‘Top Cat’

Is there something wrong with my code that isn’t specific enough or should I be taking a totally different route than the one I’m taking. This is a pivotal part of the spreadsheet…..but things can be changed. Thanks and let me know if I made sense above. I believe this is all the information. I’m using ver. 2003

 

[imback2nite]

Anybody? Anything? Ideas? This is driving me crazy! It should work.....but it doesn't!