Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
undoing the for next procedure
Guys, My macro creates 66 new wsheets and names them. How do I undo the sub? I mean if i wanted to modify code and include other commands withing the For Next Procedure, but dont want to create another 66 new sheets and name them all over again but would like to undo it and repeat the procedure with my revised code. Thanks so much!!!! Regards, Maria -- mariasa ------------------------------------------------------------------------ mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726 View this thread: http://www.excelforum.com/showthread...hreadid=521666 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
undoing the for next procedure
Hi Mariasa,
Try: '============= Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim i As Long Set WB = ThisWorkbook On Error GoTo XIT With Application .DisplayAlerts = False .ScreenUpdating = False End With For Each SH In WB.Sheets If SH.Index 3 Then SH.Delete End If Next SH XIT: With Application .DisplayAlerts = True .ScreenUpdating = True End With End Sub '<<============= --- Regards, Norman "mariasa" wrote in message ... Guys, My macro creates 66 new wsheets and names them. How do I undo the sub? I mean if i wanted to modify code and include other commands withing the For Next Procedure, but dont want to create another 66 new sheets and name them all over again but would like to undo it and repeat the procedure with my revised code. Thanks so much!!!! Regards, Maria -- mariasa ------------------------------------------------------------------------ mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726 View this thread: http://www.excelforum.com/showthread...hreadid=521666 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
undoing the for next procedure
Wow long code but worked like magic when applied after using ur code to create the 66 worksheets and name them. Thanks Norman!!!! But when I use the shorter version of OverAC to create the 66 sheets and name them first, namely Sub CommandButton1_Click() Dim counter As Integer For counter = 2 To 67 Sheets.Add ActiveSheet.Name = Sheets("Sheet1").Range("A" & counter).Value Next counter End Sub and then use ur code to undo it, i am left with sheet 67, 68, 69 named according to the funds list 947, 949 and 953. So the sheet1 which contains all the code is then eliminated. Why does that happen and is there any way to fix that? Thanks a bunch :-) Sincerely, Maria -- mariasa ------------------------------------------------------------------------ mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726 View this thread: http://www.excelforum.com/showthread...hreadid=521666 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
undoing the for next procedure
Hi Maria,
Wow long code but worked like magic when applied after using ur code to create the 66 worksheets and name them. Thanks Norman!!!! The length of code is not necessarily an indication of efficiency; indeed an inverse relationship may exist. My code could be shortened substantially by, for exaample, deleting the (non contiguous) sections: On Error GoTo XIT With Application .DisplayAlerts = False .ScreenUpdating = False End With XIT: With Application .DisplayAlerts = True .ScreenUpdating = True End With This would shorten the code but would also increase the code execution time. But when I use the shorter version of OverAC to create the 66 sheets and name them first, namely Sub CommandButton1_Click() Dim counter As Integer For counter = 2 To 67 Sheets.Add ActiveSheet.Name = Sheets("Sheet1").Range("A" & counter).Value Next counter End Sub and then use ur code to undo it, i am left with sheet 67, 68, 69 named according to the funds list 947, 949 and 953. So the sheet1 which contains all the code is then eliminated. Why does that happen and is there any way to fix that? My original sheet insertion code specified that each new sheet should be added to the end of the workbook. In consequence, I am able to delete the (now) unwanted sheets by deleting all sheets after the third sheet. The shorter code which you have used does not specify the insertion position for the new sheets and, thus, problems may be experienced if you use my suggested deletion code. In the present situation, you could delete the remaining three unwanted sheets with a one-off code: '============= Public Sub Tester04() Dim arr As Variant arr = Array("947", "949", "953") Sheets(arr).Delete End Sub '<<============= --- Regards, Norman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
undoing the for next procedure
Hi Norman, i tried deleting the 2 parts of the code as u suggested and i got the msg "Data may exist in the sheets selected for deletion. To permanently delete the data press delete" and I had to press delete 66 times to get down to my starting 3 sheets. Also with ur last suggestion - insert the code to delete the remaining 3 unwanted sheets - my workbook will be completely empty, since the sheet 1, 2 and 3 have already been deleted. Thanks for the explanation. I will use ur code for creation and naming of the sheets then since it works with ur undo method and seems to be more flexible because of all the dims thanks again!!! -- mariasa ------------------------------------------------------------------------ mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726 View this thread: http://www.excelforum.com/showthread...hreadid=521666 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Undoing formula | Excel Discussion (Misc queries) | |||
Undoing a macro | Excel Programming | |||
undoing conditional formatting | Excel Discussion (Misc queries) | |||
Undoing LINKS in Excel 2000 | New Users to Excel | |||
undoing a VB action | Excel Programming |