Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert several Sheets | Excel Discussion (Misc queries) | |||
Insert Columns in multiple sheets | Excel Worksheet Functions | |||
I want to match and insert info on 2 different sheets | New Users to Excel | |||
How many sheets can i insert??? | Excel Discussion (Misc queries) | |||
insert worksheet gives me 3 duplicate sheets not one | Excel Discussion (Misc queries) |