Need to add hyperlink using VBA code

[Bev Kaufman]

I am working in Excel 2007.
Given a row with a company name in the A cell, and a company category in the
D cell, I need to create a worksheet with the name of the company category
(D), copy the contents of a template worksheet to the new worksheet, and
create a hyperlink from the company name (A) to the new worksheet.
I've got everything working except the last bit. I have already spent at
least 2 hours looking at bulletins on this subject, but nothing that I try
works. Here is the hyperlink code as it now stands:
vCell = "A" & Trim(Str(pRow))
WS.Activate
WS.Hyperlinks.Add Anchor:=WS.Range(vCell), Address:="'" & pName & "'!A1"
pRow is the cell on the original worksheet - in this case, vCell = A17
WS is the original worksheet, not the one just created
pName is the name of the new worksheet, so I am setting Address to 'New
Company!A1' I have also tried it with just the name (no !A17).
I have also tried the hyperlinks like with Cells(pRow,1) instead of
Range(vCell). It had no effect.
This code does not bomb, it just doesn't do anything. At the end of each
test, the new worksheet exist just as I want it to be, but I cannot
automatically jump to it by clicking on the company name.
What am I doing wrong?

 

[John_John]

Try this:

WS.Hyperlinks.Add Anchor:=WS.Range(vCell), Address:="", SubAddress:="'" &
pName & "'!A1"

Ο χÏήστης "Bev Kaufman" έγγÏαψε:

 

[Bev Kaufman]

That was one of my earlier efforts. It doesn't create the hyperlink. What
it does do is place New Category'!A1 in A17 of the new worksheet.