Define worksheet as variable, then call in macro

Hello All,

I want to dim a worksheet name, and then later call it as a variable to go to in another macro.

e.g.

Dim callworksheetname as worksheets
callworksheetname = bob

Call bobsaddress()
Activate.bob.worksheet

I know the above is wrong, but you can guess what i am getting at

Any help please?

J

Hi Bugman,

Try something like:

'==========>>
Public Sub One()
Dim SH As Worksheet

With ActiveWorkbook
Set SH = .Worksheets(.Worksheets.Count)
End With

Call Two(SH)

End Sub

'------------>>

Public Sub Two(aSheet As Worksheet)

MsgBox aSheet.Name

End Sub
'<<==========

---
Regards,
Norman


"bugman" <[email protected]> wrote in
message news:[email protected]...
>
> Hello All,
>
> I want to dim a worksheet name, and then later call it as a variable to
> go to in another macro.
>
> e.g.
>
> Dim callworksheetname as worksheets
> callworksheetname = bob
>
> Call bobsaddress()
> Activate.bob.worksheet
>
> I know the above is wrong, but you can guess what i am getting at
>
> Any help please?
>
> J
>
>
> --
> bugman
> ------------------------------------------------------------------------
> bugman's Profile:
> http://www.excelforum.com/member.php...o&userid=28999
> View this thread: http://www.excelforum.com/showthread...hreadid=487283
>

Dim callworksheetname as worksheet
callworksheetname = bob

Call bobsaddress()
worksheet(callworksheetname ).Activate


or do you want to pass the worksheet name to the module?

Call bobsaddress(callworksheetname)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"bugman" <[email protected]> wrote in
message news:[email protected]...
>
> Hello All,
>
> I want to dim a worksheet name, and then later call it as a variable to
> go to in another macro.
>
> e.g.
>
> Dim callworksheetname as worksheets
> callworksheetname = bob
>
> Call bobsaddress()
> Activate.bob.worksheet
>
> I know the above is wrong, but you can guess what i am getting at
>
> Any help please?
>
> J
>
>
> --
> bugman
> ------------------------------------------------------------------------
> bugman's Profile:
http://www.excelforum.com/member.php...o&userid=28999
> View this thread: http://www.excelforum.com/showthread...hreadid=487283
>