copy sheets with a function

[soldstatic]

Hey everyone, I could really use a little help here. Didn't get any replies to my last post, so I'm trying again, hopefully I can describe my problem a little better and entice someone to help out

First off, wow I've never done so much crazy stuff with Excel! I'm kind of a newb I guess, but it is super powerful and awesome!

My function JUST WONT WORK. I can't figure out why. It may be easier to understand my problem through the code, but in words I have a function that takes in a string, if a sheet by that name exists the function needs to return cell B3 of that sheet, if the sheet does NOT exist it needs to copy a template sheet - rename it to the string - then return cell B3.

Here's my code so far:

Please Login or Register  to view this content.

I don't have the rename happening yet because i can't even get the copy to happen...

Please help!!

[Wizz]

try the following:

Please Login or Register  to view this content.

[soldstatic]

No dice. The returned string is "#VALUE" and there are no new sheets...

[shg]

You can't call the function from the worksheet; worksheet functions can only return values to the cells in which they appear, not modify any other part of the sheet or workbook.

[soldstatic]

so is it possible to call a macro with an argument from inside the function? Thus i could create a macro that does the copy etc with the name I pull from the function?

[shg]

You can call the function from a sub:

Please Login or Register  to view this content.

[soldstatic]

but i don't want to have to run a macro every time something is changed... hm.. i don't like it. i'll have to restructure my approach. Thank you for your help!!