Sort Worksheet with a wild card

[Shazam]

Hi everyone,

I import many worksheets into one workbook. But I would like the worksheet name "Foundry 02-04-06" be the first sheet in order. But the thing is the next day it will be named "Foundry 02-05-06" so it changes daily. Can the code could have wild card like find anything thats start with Foundry sort it first

Something like "Foundry*"

[XLGibbs]

If it is the only worksheet beginning with Foundry you could use...

[VBA]
Dim ws as Worksheet
For each ws in ActiveWorkbook.Worksheets
If Left (ws.Name,7) = "Foundry" then
ws.Move Before:= Sheets(1)
End if
Next ws
[/VBA]

Untested, but something like that may work for you. basically says to move the worksheet before the 1st one in the tab order.

[malik641]

Do you have more than one "Foundry" sheet with every import that you do? Cause I don't believe XLGibbs' code will sort the "Foundry" sheets themselves...just put them all upfront, in whatever order you had them in to begin with.

EDIT: Oops...sorry Gibbs, didn't see you mentioned "If it is the only worksheet with Foundry". My apologies.

[malik641]

This will work if you have that case of more than one Foundry date...except it doesn't handle different years for some reason...

Anyway, with the help of:
http://www.ozgrid.com/VBA/sort-array.htm

I adjusted some things and came up with:
[vba]Option Explicit

Sub Sort_Foundry()
Dim ws As Worksheet
Dim MyArray() As String
Dim str1 As String, str2 As String
Dim i As Long, j As Long

For Each ws In ActiveWorkbook.Worksheets
If Left(ws.Name, 7) = "Foundry" Then
i = i + 1
ReDim Preserve MyArray(i)
MyArray(i) = ws.Name
End If
Next ws

For i = 0 To UBound(MyArray) - 1
For j = i To UBound(MyArray) - 1
If UCase(MyArray(j)) < UCase(MyArray(i)) Then
str1 = MyArray(i)
str2 = MyArray(j)
MyArray(i) = str2
MyArray(j) = str1
End If
Next j
Next i

For i = 0 To UBound(MyArray)
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = MyArray(i) Then
ws.Move Before:=Sheets(i)
End If
Next ws
Next i

End Sub[/vba]

[Shazam]

Thank You XLGibbs it works well. Also malik641 there is only one worksheet tab name so it should work fine.

One more thing XLGibbs I have worksheets that contains most of charts that are group together and the vaules are percentage. But when I save the workbook to another folder and open it from there the vaules loses the format of being percentage. Why is that?

[XLGibbs]

Sometimes with label formatting, if the labels are linked to the source data, and the data is in an external file, the formatting from the cells does not carry forward to the transported chart.

You might have to manually format the series to show as percentage to alleviate the problem.

You might also like this:
http://vbaexpress.com/kb/getarticle.php?kb_id=831

[Shazam]

How about a workbook event code that it could automatically format all thoses vaules into percentages? If not then it would have to do it manually. The link you provided is great thats a very nice code it will be very useful. Can that toolbar could be located in the print preview because I have 8 to 15 worksheet tabs ( depends what day it is ) and its easier and more clear to see the whole chart in the print preview? If it not thats ok. Thank You so much XLGibbs.

malik641,

I will test your code.

[XLGibbs]

That code can be tied to a print preview as well if you like, you would just have to modify the macro below to include PrintPreview

To include
Sub ChartCall(ByVal Ch As String)

On Error Goto ErrHandler
Charts(Ch).Activate
ActiveWorksheet.PrintPreview
'this macro is assigned to each button on the created toolbar,
'each macro is assigned the sheet name as a variable to pass to the routine...
Exit Sub
ErrHandler: 'If a chart rename or delete was not captured by the workbook events
CreateSheetList
End Sub


Which would active the chart sheet and set up PrintPreview

[Shazam]

Thanks XLGibbs I will implement your code at work tomorrow. Thank You very much.