Move Differently Named Sheets In Workbook However Some Could Be Missing (Then move to the next)

[TkdKidSnake]

Hi all,

I am trying to use a macro to move a range of excel tabs to the front of the workbook however some of the tabs could be missing due to no information.

If I simply record the macro by moving them manually if the procedure arrives at one that is missing it would report an error.

So in essence I need it to look for the tab and if its there move and if its not move onto the next until it arrives at the last one.

Any idea's anyone!

Thanks in advance for any help that can be given.

 

[p45cal]

You need a little function to test for the existence of a sheet before you try to do anything with it:

Function SheetExists(SheetName As String) As Boolean
Dim Sh As Object
On Error Resume Next
Set Sh = Sheets(SheetName)
If Not Sh Is Nothing Then SheetExists = True
End Function

and use it in you code thus:

If SheetExists("Sheet1") Then
  'do something here
End If

or if the sheetname is in a variable:

mySheetName = "Sheet1"
If SheetExists(mySheetName) Then
  'do something here
End If

 

[kawa13]

Don't know if this will help, but I use this code in an Excel Navigation tool I have. This is executed with a button to offer the user the choice to sort sheets alphabetically A-Z or Z-A


Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then sort in ascending order.
'
If iAnswer = vbYes Then
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
'
' If the answer is No, then sort in descending order.
'
ElseIf iAnswer = vbNo Then
If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
End If
Next j
Next i
End Sub

 

[kawa13]

Don't know if this will help, but I use this code in an Excel Navigation tool I have. This is executed with a button to offer the user the choice to sort sheets alphabetically A-Z or Z-A


Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then sort in ascending order.
'
If iAnswer = vbYes Then
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
'
' If the answer is No, then sort in descending order.
'
ElseIf iAnswer = vbNo Then
If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
End If
Next j
Next i
End Sub

 

[Peter_SSs]

Try this

Sub MoveSheetsToFront()
  Dim FrontSheets
  Dim i As Long
  
  FrontSheets = Array("Main Data", "List", "Sheet1") '<- Add more if you want
  
  Application.ScreenUpdating = False
  On Error Resume Next
  For i = UBound(FrontSheets) To LBound(FrontSheets) Step -1
    Sheets(FrontSheets(i)).Move Before:=Sheets(1)
  Next i
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

 

[TkdKidSnake]

thank you for your response I will give them a try!

 

[TkdKidSnake]

Hi P45cal,

I posted on the thread thanking you all for your help!

But thanks again all the post have been a massive help.

 

[p45cal]

Hi P45cal,
I posted on the thread thanking you all for your help!

TkdKidSnake, you didn't need a separate acknowledgement, my signature isn't directed at you personally! , it appears after all my posts. Glad to hear you got it working.

 

[TkdKidSnake]

Hi p45cal

Can this be used in array as we can have over 250sheets in the workbook and if this code has to be used for individual sheet it would be very time consuming.


Thanks again for your help!

 

[Peter_SSs]

Can this be used in array as we can have over 250sheets in the workbook and if this code has to be used for individual sheet it would be very time consuming.

Perhaps I'm not understanding what you mean by this but my code in post #5 does use an array to specify the sheets you want at the front. If it doesn't do what you want, please explain in what way it fails and a better suggestion can hopefully be developed.