Renaming / Resorting Sheet Tabs via VBA__Perfect! Than

[Legacy 98055]

Hi Guys.
Board search is not working.

How do I do this?
Any example will do...
Thanks,
Tom
This message was edited by TsTom on 2002-03-27 09:21

 

[Joe Was]

Sub mySheetData()
'
'By Joe Was

'This adds a sheet and names it "Test."
Sheets.Add.Name = "Test"

'This selects your new sheet and moves it after sheet "Sheet3," which could be any sheet name.
Sheets("Test").Select
Sheets("Test").Move After:=Sheets("Sheet3")

'this selects the sheet with the data and its range.
Sheets("Sheet1").Select
Range("A1:A7").Select

'This will copy and paste the data to your new sheet "Test."
Selection.Copy
Sheets("Test").Select
ActiveSheet.Paste

'At this point your data will be on the new sheet and selected for the next step.

End Sub

This askes the user for a new sheet name and makes the new sheet.

Sub IBoxSheet()
Dim mySheet As String
'This code asks the user for a new Sheet name.
'Then makes a new Sheet with the users inputed name.

mySheet = Application.InputBox(prompt:="Enter the name of your new Sheet: ", Title:="Add Sheet!", Type:=1 + 2)
Sheets.Add.Name = mySheet
End Sub


Hope this helps. JSW
This message was edited by Joe Was on 2002-05-29 19:26

 

[LarryJ]

To rename a worksheet, change it's Name property.

wks.Name = "New Name"

As far as sorting is concerned, there is no easy way that I know of (at least in 97, not real firm on 2000/2002).

You will have to manually loop through all the sheets and create a sorted list of names (in an array?) then use the .Move method of the worksheet to move them before/after the proper sheets. I would guess that it's a good bit of code.

 

[Matt]

Renaming

Sheets("Sheet1").Name = "Bob"

Resorting

Sheets("Bob").Select
Sheets("Bob").Move After:=Sheets(3) 'moving sheet "Bob" after the third sheet

HTH

Matt