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
'<<=============

 

[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
'<<=============

 

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