Go to current sheet

C

caroparrado

New Member
Joined
May 10, 2015
Messages
5
Hello,

sorry I am still newbie at VBA, do you know how can I do to activate and select the current sheet when I run the Macros. Usually when I run the macros in a different sheet is taking me to the first one. Below you can see the code:

Sub Run()
Dim sName As String 'name of sheet to show
Dim sName2 As String 'name sheet 2


Dim x As Integer

'cycle through all sheets (except for 1st one) and hide
For x = 2 To Sheets.Count
Sheets(x).Visible = False
Next x

'get name of chosen sheet
sName = Worksheets("Select Region").Range("E3").Value
sName2 = Worksheets("Select Region").Range("G3").Value


'make selected sheet visible and active
With ActiveWorkbook.Sheets("Operational Dashboard->")
.Visible = True

End With

With ActiveWorkbook.Sheets(sName)

.Visible = True


End With

With ActiveWorkbook.Sheets(sName2)
.Visible = True

End With

With ActiveWorkbook.Sheets("Executive Dashboard ->")
.Visible = True

End With

With ActiveWorkbook.Sheets("External Output")
.Visible = True

End With

With ActiveWorkbook.Sheets("Productivity Output")
.Visible = True

End With

With ActiveWorkbook.Sheets("Growth Output")
.Visible = True

End With

With ActiveWorkbook.Sheets("Quality Output")
.Visible = True


End With

With ActiveWorkbook.Sheets("1. Support Services Excellence")
.Visible = True


End With

With ActiveWorkbook.Sheets("2. Delivery Excellence")
.Visible = True

End With

With ActiveWorkbook.Sheets("3. Resourcing_Capacity")
.Visible = True

End With

With ActiveWorkbook.Sheets("4. IB Sales")
.Visible = True

End With

With ActiveWorkbook.Sheets("5. Service Subcontracting")
.Visible = True

End With


With ActiveWorkbook.Sheets("5 Pillars Dashboard->")
.Visible = True

End With

'Select the active sheet

Dim ActSheet As Worksheet


Set ActSheet = ActiveSheet
ActSheet.Activate
ActSheet.Select


Thanks!!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
With VBA, you rarely need to use .Select or .Activate - they are just actions that can be applied to the object (cell, worksheet, file etc). See if this code suits your needs:
Code: 
Sub Run_v2()
    
    Dim shtNames(1 To 2)    As String
    Dim x                   As Long
    Dim var                 As Variant
    Dim wCurrent            As Worksheet
    
    Set wCurrent = ActiveSheet
    
    'Disable screenupdating
    Application.ScreenUpdating = False
    
    'Hide all sheets except 1st sheet
    For x = 2 To Worksheets.Count
        Sheets(x).Visible = False
    Next x
    
    'Get sheet names
    With Sheets("Select Region")
        shtNames(1) = .Range("E3").value
        shtNames(2) = .Range("G3").value
    End With
    
    'Looop through sheet names to make visible
    For Each var In Sheets(Array("Operational Dashboard->", shtNames(1), shtNames(2), "Executive Dashboard->", _
            "External Output", "Productivity Output", "Growth Output", "Quality Output", "1. Support Services Excellence", _
            "2. Delivery Excellence", "3. Resourcing_Capacity", "4. IB Sales", "5. Service Subcontracting", "5 Pillars Dashboard->"))
            
            Sheets(var).Visible = True
        
    Next var
                
    'Return to activesheet
    If ActiveSheet.Name <> wCurrent.Name Then wCurrent.Select
        
    'Re-able screenupdating
    Application.ScreenUpdating = True
    
    Erase shtNames
    Set wCurrent = Nothing

End Sub
 
Upvote 0
Hello,

Thanks for the answer, when I try to apply this code it shows me the following error: Subscript out of Range

Do you maybe know why is this error appearing?

caroparrado said:
Hello,

sorry I am still newbie at VBA, do you know how can I do to activate and select the current sheet when I run the Macros. Usually when I run the macros in a different sheet is taking me to the first one. Below you can see the code:

Sub Run()
Dim sName As String 'name of sheet to show
Dim sName2 As String 'name sheet 2


Dim x As Integer

'cycle through all sheets (except for 1st one) and hide
For x = 2 To Sheets.Count
Sheets(x).Visible = False
Next x

'get name of chosen sheet
sName = Worksheets("Select Region").Range("E3").Value
sName2 = Worksheets("Select Region").Range("G3").Value


'make selected sheet visible and active
With ActiveWorkbook.Sheets("Operational Dashboard->")
.Visible = True

End With

With ActiveWorkbook.Sheets(sName)

.Visible = True


End With

With ActiveWorkbook.Sheets(sName2)
.Visible = True

End With

With ActiveWorkbook.Sheets("Executive Dashboard ->")
.Visible = True

End With

With ActiveWorkbook.Sheets("External Output")
.Visible = True

End With

With ActiveWorkbook.Sheets("Productivity Output")
.Visible = True

End With

With ActiveWorkbook.Sheets("Growth Output")
.Visible = True

End With

With ActiveWorkbook.Sheets("Quality Output")
.Visible = True


End With

With ActiveWorkbook.Sheets("1. Support Services Excellence")
.Visible = True


End With

With ActiveWorkbook.Sheets("2. Delivery Excellence")
.Visible = True

End With

With ActiveWorkbook.Sheets("3. Resourcing_Capacity")
.Visible = True

End With

With ActiveWorkbook.Sheets("4. IB Sales")
.Visible = True

End With

With ActiveWorkbook.Sheets("5. Service Subcontracting")
.Visible = True

End With


With ActiveWorkbook.Sheets("5 Pillars Dashboard->")
.Visible = True

End With

'Select the active sheet

Dim ActSheet As Worksheet


Set ActSheet = ActiveSheet
ActSheet.Activate
ActSheet.Select


Thanks!!
Click to expand...
 
Upvote 0
I'm guessing it's because a sheet name is wrong, check all the sheet names in the code are correct. What exact line does that error occur on?
 
Upvote 0
I checked and the names of the sheets are fine, the line where the error occurs is this one:

For Each var In Sheets(Array("Operational Dashboard->", shtNames(1), shtNames(2), "Executive Dashboard->", _
"External Output", "Productivity Output", "Growth Output", "Quality Output", "1. Support Services Excellence", _
"2. Delivery Excellence", "3. Resourcing_Capacity", "4. IB Sales", "5. Service Subcontracting", "5 Pillars Dashboard->"))


Thanks a lot for your help!!

JackDanIce said:
I'm guessing it's because a sheet name is wrong, check all the sheet names in the code are correct. What exact line does that error occur on?
Click to expand...
 
Upvote 0
Hi JackDanIce,

I changed the array to the following and now is working as I wanted.

Sheets("5 Pillars Dashboard->").Visible = True
Sheets("1. Support Services Excellence").Visible = True
Sheets("2. Delivery Excellence").Visible = True
Sheets("3. Resourcing_Capacity").Visible = True
Sheets("4. IB Sales").Visible = True
Sheets("5. Service Subcontracting").Visible = True
Sheets(shtNames(1)).Visible = True
Sheets(shtNames(2)).Visible = True
Sheets("Executive Dashboard ->").Visible = True
Sheets("External Output").Visible = True
Sheets("Productivity Output").Visible = True
Sheets("Growth Output").Visible = True
Sheets("Quality Output").Visible = True
Sheets("Operational Dashboard->").Visible = True


Thanks a lot for your help!!!
 
Upvote 0
JackDanIce, I've just scanned your code quickly (it's a lot tidier!) but it would appear that Var contains a Sheets object so shouldn't

Code: 
Sheets(var).Visible = True

be

Code: 
var.Visible = True
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Excel Crashing after SaveAs
Replies
0
Views
194
ampd
A
D
  • Solved
Run-time error 9, Subscript out of range - Copying sheet from closed workbook to active workbook as new sheet
Replies
9
Views
298
dvward
D
N
  • Solved
VBA Question
Replies
3
Views
126
RoryA
RoryA
M
  • Question
Creating and emailing Excel sheet with only visible data
Replies
0
Views
179
mecerrato
M
M
  • Question
Document not Saved!
Replies
0
Views
231
MAlhash
M

Forum statistics

Threads
1,214,944
Messages
6,122,391
Members
449,080
Latest member
Armadillos

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top