VBA Moving Sheets (and Hiding/Unhiding)

[jrnyman]

I have to quick questions. At the end of one of my macros I need to move three sheets to the very front of the line and scroll to see those tabs. The sheets in order are "Macro", "Data", and "MyChart". I've been using:

Sheets("Macro").Select
Sheets("Macro").Move before:=Sheets(1)
...

but I get a runtime 1004 error. Any ideas?

My second question is if there is a way to hide/unhide sheets in VBA. I have about 35 sheets in my workbook. I would like to hide them all, but the macros need them to not be hidden, so can I just have them appear for the macro to use? Thanks for the help.

 

[Colo]

Hi. Please Try.

<pre>
Sub WorkSheetsShow()
Dim sh As Worksheet
For Each sh In Worksheets
sh.Visible = xlSheetVisible
Next
End Sub

Sub WorkSheetsHidden()
Dim sh As Worksheet, arr
arr = Array("Macro", "Data", "Chart") 'Change Name of Sheet you don't want hide.
For Each sh In Worksheets
If IsError(Application.Match(sh.Name, arr, 0)) Then
sh.Visible = xlSheetHidden
End If
Next
End Sub
</pre>

 

[robfo0]

Are there other moves in your code, because that works when i use it. As for hide and unhide, yes you can do it from VBA:

Sheets("Macro").Visible = False

and

Sheets("Macro").Visible = True to unhide it

 

[jrnyman]

The moves I have are:

Sheets("Macro").Select
Sheets("Macro").Move before:=Sheets(1)
Sheets("MyChart").Select
Sheets("MyChart").Move before:=Sheets(2)
Sheets("Data").Select
Sheets("Data").Move before:=Sheets(3)

I'm really not sure why I'm having problems with this. Are there other factors that could complicate a simple move?

 

[jrnyman]

I figured it out. I had several combo-boxes (from Control Toolbox) on the Macro sheet. If the last activity on that sheet was choosing something from a combo-box, the move command hit a runtime error. If I simply inserted any cell selection before the move, it worked flawlessly. Interesting...

 

[Dave Hawley]

Hi

The code you have should work, but make sure you do not have any sort of protection applied (Workbook in particular). The code could be shortened to:

Sub MoveSheets()

Sheets("Macro").Move before:=Sheets(1)
Sheets("MyChart").Move before:=Sheets(2)
Sheets("Data").Move before:=Sheets(3)

End Sub

Which brings me to my next point. You should not need to unhide sheets to run a macro in VBA. In fact it is very rare you need to Select or Activate an Object to change its Properties or run Methods.

In regards to your sheet hide/unhide code try this:

Sub ToggleHide()
Dim wsSheet As Worksheet

    For Each wsSheet In ActiveWorkbook.Worksheets
        wsSheet.Visible (Not wsSheet.Visible)
    Next wsSheet

End Sub

One very important point here is that you CANNOT hide all sheets in a Workbook, there must be at least one visible.