undoing the for next procedure

[mariasa]

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

[Norman Jones]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[mariasa]

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

[Norman Jones]

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

[mariasa]

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