Activate Visible Sheet only?

[Dennis]

I have 10 sheets in a workbook. All sheets have random names in their tabs.

If I'm looking at one of the 10 sheets and I want to execute a macro on that
sheet what is the VBA code to find the name of the sheet I'm looking at?

Here is a snippet of code that will work for sheet #2 but if I'm not looking at
sheet(2) and I execute the macro how do I get the macro to execute on the sheet
I'm looking at?

OS = Sheets(2).Name
Set curwk = Sheets(OS)
Sheets(OS).Activate
With curwk
...
'''

Thanks in advance for any help.

Dennis

 

[Juan Pablo González]

I have 10 sheets in a workbook. All sheets have random names in
their tabs.

If I'm looking at one of the 10 sheets and I want to execute a macro
on that sheet what is the VBA code to find the name of the sheet I'm
looking at?

Here is a snippet of code that will work for sheet #2 but if I'm not
looking at sheet(2) and I execute the macro how do I get the macro to
execute on the sheet I'm looking at?

OS = Sheets(2).Name
Set curwk = Sheets(OS)
Sheets(OS).Activate
With curwk
...
'''

Thanks in advance for any help.

Dennis

Use ActiveSheet instead of Sheets(2)

 

[Dennis]

When I use OS = ActiveSheet.Name it returns the name of the first sheet even if
the first sheet is not being displayed. In other words I have sheet #5 whose
name is say "xxxx" visible and OS = ActiveSheet.Name returns the name of the
first sheet which is say "qqqq"

Dennis

 

[Dennis]

Actually what happens is that a subroutine in my main VBA routine activates
Sheets(1), performs some calcs on sheets(1), and saves the calc results in
global space that are needed in the sheet that is visible. The only way I can
get back to the sheet that is visible is to first save the name of the visible
sheet with a myName=Activesheet.Name BEFORE I call the subroutine. After I
return from the subroutine I have to perform a Set curwk=Sheets(myName) and
Sheets(myName).Activate in order to run my VBA code on the visible sheet myName.

Is there an easier way?

Dennis

 

[Dave Peterson]

A lot of times you don't have to activate sheets to work on them.

But instead of using a variable to keep track of the name of the worksheet, I
use an object variable to keep track of the worksheet:

dim curWks as worksheet
set curwks = activesheet
'do lots of stuff
curwks.select
'or even
application.goto curwks.range("a1"), scroll:=true

 

[Dennis]

Thanks Dave. Your method is a little easier than the .Name method.

Dennis