Run-time error

[Len Silva]

I have a workbook with a list of names in Sheet 1, Col A.

I also have a macro that creates and names a new sheet for every name. That is working well.

Then I have this macro that creates a hyperlink from the name to the sheet. It also works well but pops an error message after it runs.

Run-time error '5':
Invalid procedure call or argument

The debugger highlights lines 4 and 5.

Here is the code:

Please Login or Register  to view this content.

This is some code I found online but I don't completely understand it.

Thanks for all your help so far with this project.

Len Silva

[AB33]

If you run the code on its own, it does not error.

[buran]

It works fine for me. Can you upload sample workbook that reproduce the error?

Sorry, AB33 :-)

[Len Silva]

Thanks,

Here is a cut down version with the names changed. The real one is about 100 sheets and will grow to 400 by the end of the year.

I run the macro Sheet1.AddSheet which uses the template in sheet 2 to create new sheets.

Then I run the macro AddHyperlink to sheet 1

I also run these macros whenever I add new names to sheet 1

Payment Log Test.xlsm

Len Silva

[buran]

OK, the problem is that you have number in A10 (last cell in column A). In the example this cell contains number 8. It tries to set ws to Worksheets(c.Value) and what happens is that there is Worksheets(8), i.e. it assigns the worksheet with index 8 to ws. Your check ws Is nothing is False and it doesn't create worksheet named 8.
After that it tries to create hyperlink to worksheet named 8 and such sheet does not exist. here comes the error. Note that if this number is not integer or is greater than max index of worksheets collection it will rise error during worksheet creation sub.
If you are going to have subtotal in column A, change your code that creates worksheets to reflect that fact.

[Len Silva]

Can you help me fix it? How about if I use a named range (clients) in Sheet 1? Could you rewrite the AddHyperlink macro to reflect that?
I definitely want to keep the tables format with totals as it makes adding new names and copying down formulas easy.

I'm reading VBA for Dummies as fast as I can but I'm not getting it yet.

Thanks.

[buran]

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[AB33]

The addsheet code will tend to create errors. The error is masked by skipping the error.
If you remove the error, the code will not move a single line as you can not set to non-existence object.

Please Login or Register  to view this content.

[Len Silva]

Thank you all. I am going to learn this, but I'm in my seventies and it doesn't come as easy as it once did.