Problem when insert sheets by macro

[Harshad]

When i run following macro on the sheet 1, all the sheet comes in reverse
direction, means, sheet6, sheet5, sheet4,....Sheet1. i want it as sheet1,
sheet2, sheet3, sheet4, sheet5, sheet6.............etc

The macro i got from this discussion group is as:

Sub Sheet_addition()
Dim i As Long
On Error GoTo endit
Application.ScreenUpdating = False
For i = 1 To 52
Sheets.Add
Next i
endit:
Application.ScreenUpdating = True
End Sub

 

[Mike H]

Maybe

Sheets.Add after:=ActiveSheet

Mike

 

[Roger Govier]

Hi

Try
Sub Sheet_addition()
Dim i As Long
On Error GoTo endit
Application.ScreenUpdating = False
For i = 1 To 52
Sheets.Add after:=Sheets(i)
Next i
endit:
Application.ScreenUpdating = True
End Sub

 

[Harshad]

Dear MIke,
Thank you,
But still sheet 4 comes before sheet3.

Harshad

 

[Mike H]

Hi,

Im confused. The line I gave you will insert sheets in ascending numerical
order to the right of the sheet you started on. What do you want it to do?

Mike

 

[Harshad]

Thank you roger,

Its working nicely.

Hi

Try
Sub Sheet_addition()
Dim i As Long
On Error GoTo endit
Application.ScreenUpdating = False
For i = 1 To 52
Sheets.Add after:=Sheets(i)
Next i
endit:
Application.ScreenUpdating = True
End Sub

 

[Mike H]

And this is different to the solution I proposed!! My confusion grows.

 

[Roger Govier]

Hi Mike

I think it is just a perception by the OP.
If sheet2 was the active sheet when he ran your macro, then 4 would come
before 3 (as would 5, 6, 7 etc)

Because I was inserting after sheet(i), the insertion would always come
directly after Sheet1 which would also make Sheet4 (assuming three sheets in
Workbook to start) come before both sheets2 and 3
Maybe he tried my macro on a workbook with a single sheet, then all would
appear to be correct, and perhaps he tried yours on a workbook with 3
sheets, and sheet2 was active at the time.
Who knows!!!

 

[Nastech]

Hi,

Im confused. The line I gave you will insert sheets in ascending numerical
order to the right of the sheet you started on. What do you want it to do?

Mike

 

[Harshad]

Dear Mike,

Your code also works very nicely when the sheet3 is active sheet, in case
of Roger, if any sheet is active the code runs very smoothly. My mean to say
that,
When i open new excel file, by default 3 sheet file opens, on which Sheet1
is active.
If i use your code on sheet1, the result is Sheet1, sheet4,
sheet5,.........,Sheet2, Sheet3.


Thank you