+ Reply to Thread
Results 1 to 5 of 5

undoing the for next procedure

  1. #1
    Registered User
    Join Date
    02-20-2006
    Posts
    29

    Question 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

  2. #2
    Norman Jones
    Guest

    Re: 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" <mariasa.24luyy_1142248501.4282@excelforum-nospam.com> wrote in
    message news:mariasa.24luyy_1142248501.4282@excelforum-nospam.com...
    >
    > 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. #3
    Registered User
    Join Date
    02-20-2006
    Posts
    29

    Question

    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

  4. #4
    Norman Jones
    Guest

    Re: 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. #5
    Registered User
    Join Date
    02-20-2006
    Posts
    29

    Arrow

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1