Dave, Tim, & Gary's Student-

Thank you all for your replies- I couldn't even figure out one way to do it,
and you all have shown me three ways to do it! I appreciate your help, and
now I'm back on track!

Best,
Keith

"Dave Peterson" wrote:

I'd pass it as an object:

Option Explicit
Sub testme()
Call CleanUpSheets(CodeNameOfSheet:=asdf)
End Sub
Sub CleanUpSheets(CodeNameOfSheet As Object)
MsgBox CodeNameOfSheet.Name
End Sub

I don't think you can get more specific than that (but I'm waiting for a
correction).

There are lots of types of sheets (worksheets, macro sheets, chart sheets,
....). And each of these have a codename.

ker_01 wrote:

Excel 2003, and probably just a syntax issue. Googled but wasn't able to find
relevant content.

I recorded a macro to grab a bunch of sheet formatting. I have many similar
sheets that all need to be formatted the same, so I want to use the recorded
macro and just pass the worksheet name as a parameter- so after I add all the
data to each worksheet, I can format it and move on.

In my limited experience with passing values between subs/functions I know
the value has to be the same type (if a function is expecting an integer
value, I not only have to pass it an integer, but I actually have to define
the variable in the source procedure as an integer (ok, I admit I rarely use
option strict on).

So, I tweaked the recorded procedure name to be:
Sub CleanUpSheets(mWorksheet As Worksheet)

and in my main procedure I use the sheet codename (because users will be
changing the tab names), and so I use:
...
CleanUpSheets(Sheet92)

which results in a run time error 438: object doesn't support this property
or method.

I didn't define the sheet, because it is the sheet codename. Just for kicks,
I tried it anyway: Dim Sheet92 as worksheet ...but then I get a runtime error
91 when I tried to access Sheet92 in the main code.

So... what is the proper syntax (on both subs) to pass a worksheet as a
parameter, so the receiving sub knows which worksheet to format?

Many thanks,
Keith

--

Dave Peterson