Problem when insert sheets by macro
 

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

Problem when insert sheets by macro
 

Maybe

Sheets.Add after:=ActiveSheet

Mike

"Harshad" wrote:

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

Problem when insert sheets by macro
 

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

--
Regards
Roger Govier

"Harshad" wrote in message
...
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

Problem when insert sheets by macro
 

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

Harshad

"Mike H" wrote:

Maybe

Sheets.Add after:=ActiveSheet

Mike

"Harshad" wrote:

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

Problem when insert sheets by macro
 

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" wrote:

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

Harshad

"Mike H" wrote:

Maybe

Sheets.Add after:=ActiveSheet

Mike

"Harshad" wrote:

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

Problem when insert sheets by macro
 

Thank you roger,

Its working nicely.


"Roger Govier" wrote:

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

--
Regards
Roger Govier

"Harshad" wrote in message
...
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

Problem when insert sheets by macro
 

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

"Harshad" wrote:

Thank you roger,

Its working nicely.


"Roger Govier" wrote:

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

--
Regards
Roger Govier

"Harshad" wrote in message
...
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

Problem when insert sheets by macro
 

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!!!


--
Regards
Roger Govier

"Mike H" wrote in message
...
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" wrote:

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

Harshad

"Mike H" wrote:

Maybe

Sheets.Add after:=ActiveSheet

Mike

"Harshad" wrote:

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

Problem when insert sheets by macro
 

"Mike H" wrote:

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" wrote:

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

Harshad

"Mike H" wrote:

Maybe

Sheets.Add after:=ActiveSheet

Mike

"Harshad" wrote:

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

Problem when insert sheets by macro
 

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
"Mike H" wrote:

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

"Harshad" wrote:

Thank you roger,

Its working nicely.


"Roger Govier" wrote:

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

--
Regards
Roger Govier

"Harshad" wrote in message
...
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