Thread: How do you capture the name of the current worksheet in VBA?
View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.programming
Chip Pearson Chip Pearson is offline
external usenet poster
  
Posts: 7,247
Default How do you capture the name of the current worksheet in VBA?
Set wsht = wbk.Worksheets(strActiveWorksheet)

You can simplify this to

Set wsht = ActiveSheet

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Fri, 25 Sep 2009 00:21:15 -0700, "Paul" wrote:

Ok, I finally got it to work.

I first tried

Set wsht = wbk.Worksheets(ActiveSheet.Name)

and that didn't work.

So then I assigned ActiveSheet.Name to a string variable

strActiveWorksheet = ActiveSheet.Name

and then usd the variable in the expression

Set wsht = wbk.Worksheets(strActiveWorksheet)

and it worked.

Thanks for providing me with the solution, Kevin.

Paul

Reply With QuoteReply With Quote