I would like to, using VBA, assign a hyperlink to an excel cell that links to another worksheet within the same workbook. I've tried the forums, and combined with a previous post and MS Excel Help File, I've come up with the following attempt:
strHyper2 is previously defined:
With Worksheets("Test Destination")
.Hyperlinks.Add Anchor:=.Cells(counter + 4, 5), Address:=strHyper2, _
TextToDisplay:=(#1/1/2007# + counter)
My spreadsheet file is "WeatherTester.xls", my worksheet is "1", and "A1" is the desired link location for the cursor.
strHyper2 = "[WeatherTester.xls]1!A1"
Counter is an integer.
I get the error "Invalid procedure call or argument"
I also tried to enter a hyperlink directly through the formula property, but Excel didn't like the single quotations I had to use within the HYPERLINK function. The hyperlink formula works in the following form: