Creating new sheet and update hyperlink with a command button

[swizard26]

My data is arranged in the following columns.

Sorted by Item number within Category.

A B C D
s/n user product Details
1 '11111' AAAA Hyper1
2 '22222' BBBB Hyper2
3 '33333' CCCC Hyper3
YY '44444' DDDD XX

With a click of command button,
1) VBA will look for the new s/n, in this case YY with is blank initially.
2) Then copy a current template in the workbook with name YY created.
3) cell A1 in sheet YY will show user '44444'
4) New hyperlink create to link the source page to the newly create sheet YY

Currently, I encounter problem at
step 3,
In cell A1, #name? appear rather than the user name.
Step 4,
compilation error.

I have attached my program here.
Can you please help to point out my mistake or suggest another way?
Any help would be appreciated.
Thanks


Private Sub CommandButton1_Click()

'get the new row #
x = 4
y = 1
z = 0
Do While Cells(x, y).Value <> ""
x = x + 1
z = z + 1
Loop

'go to the next cell for new serial #
Cells(z + 2, 1).Select
ActiveCell.FormulaR1C1 = z + 1

'copy current template
Sheets(z + 2).Copy After:=Sheets(z + 2)
Sheets(z + 3).Name = "" & z + 1 & ""

'to select cell A1 in the new sheet and copy name from column E for sheet Source
Worksheets(z + 5).Range("A1").FormulaR1C1 = "=Source!Cells(z + 4, 5)"

'add hyperlink
Selection.AutoFill Destination:=Range(Cells(z, 4), Cells(& z + 1 &, 4)), Type:=xlFillDefault
Cells(& z + 1 &, 4).Select
Selection.Hyperlinks(1).SubAddress = "'z + 1'!A1"

End Sub

CSW