ADD NEW SHEET AND PUSH IT TO LAST

  • Thread starter Thread starter Girish Bantwal
  • Start date Start date
G

Girish Bantwal

HI
What is the macro for adding new sheets and automatically pushing it
to the end. Every time excel adds a new sheet this is the format in
which sheets are added C110 C109 C108 WHICH I DONT WANT i want sheets
to come in the following fashion C108 C109 C110. I have written a VB
code pls check up and tell me where i am going wrong

Sub Macro1()
'
Dim i As Integer
Dim LastSheetName As String
Dim NewSheetName As String
Dim PromptForName As String
Dim AddedSheetName As String

i = 108

For i = 108 To 110

Sheets.Add
AddedSheetName = ActiveSheet.Name
NewSheetName = "C" & i
Sheets(AddedSheetName).Name = NewSheetName
Next
End Sub

THANKS
GIRISH
 
HI

Try making these couple of changes for a start

Sub Macro1()

Dim i As Integer
Dim Counter As Long
Dim Counter1 As Long

For i = 108 To 110

Sheets.Add
ActiveSheet.Name = "C" & i

For Counter = 1 To Sheets.Count - 1
For Counter1 = Counter + 1 To Sheets.Count
If Sheets(Counter1).Name < Sheets(Counter).Name Then
Sheets(Counter1).Move before:=Sheets(Counter)
Sheets(Counter + 1).Move before:=Sheets(Counter1)
End If
Next Counter1
Next Counter

Next
End Sub

and then have a play to see what else you want to do with it from there

Regards

Gav !!
 
Hi Grish,

This does it

Sub Macro1()
Dim i As Integer
Dim LastSheetName As String
Dim NewSheetName As String
Dim PromptForName As String
Dim AddedSheetName As String

For i = 108 To 110

Sheets.Add after:=Worksheets(Worksheets.Count)
AddedSheetName = ActiveSheet.Name
NewSheetName = "C" & i
Sheets(AddedSheetName).Name = NewSheetName
Next

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top