Deleting original sheets

[Jeff Kelly]

I have a workbook which has several original sheets of data

Using "TotSheets = Worksheets.Count' I count these sheets and call it
"mycount"
With a macro I add new sheets. How would I amend the macro at the end to
delete the original sheets.

72 years old and just learning so go easy on me.

 

[Sandy Mann]

One way would be to rename to original sheets, add the new sheets and then
delete all sheets renamed sheets as in this small macro:

Sub Deletesheets()
Application.ScreenUpdating = False

No = 1

For Each sh In Worksheets
sh.Name = "DeleteThis" & No
No = No + 1
Next sh

For x = 1 To 3
Sheets.Add
Next x

Application.DisplayAlerts = False

For Each sh In Worksheets
If Left(sh.Name, 10) = "DeleteThis" Then
sh.Delete
End If
Next sh

Application.DisplayAlerts = True

Application.ScreenUpdating = True
End Sub

Out of curiosity why are you counting the sheets using TotSheets then
copying the number to mycount? Why not use mycount in the first place?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[JLGWhiz]

If your sheets are added after the last original sheet
then you could just add in a couple of lines like:

For i = 1 To myCount
Sheets(i).Delete
Next

If you add the sheets before sheet one then the code
would look like.

For i = Sheets.Count - myCount To SheetsCount
Sheets(i).Delete
Next

The sheet index number runs from left to right as the
tabs appear, regardless of what the tab shows. So you
can either start at sheet 1 if all additions were on
the back end of the line, or start with the next number
after myCount if the sheets were all added to the front
end of the line. The only problem would be if they are
interspersed or alternated front and rear when added.

 

[Dave Peterson]

That first suggestion could cause trouble:

For i = 1 To myCount
Sheets(1).Delete
Next i

(Always deleting the first sheet (of the remaining sheets) will be safe.)

 

[the pest]

Brilliant, Dave. Works a treat. I promise never to criticise Excel again


+++++++++++++++++++++++

 

[JLGWhiz]

Hi Dave, I am not following you on the logic. If the added sheets are after
the originals, then the original sheets should be indexed 1 To myCount. To
delete those original sheets would require starting at sheets(1), ergo

For i = 1 To myCount
Sheets(i).Delete
Next

Did I overlook something?

 

[Dave Peterson]

Say you have 3 sheets 1,2,3 and add 2 more A,B (in nice order--1,2,3,A,B)

If you use this:
you'll delete sheets(1)
and 2,3,A,B
will remain

Then you'll delete sheets(2). That's 3 (the second one from the left.)
So you'll have
2,A,B
remaining

Then you'll delete sheets(3). That's B.
So you'll have
2,A

Probably not what you want.

 

[JLGWhiz]

Oh yeah, after 1 is gone, 2 becomes 1 and 3 becomes 2, so the second
iteration takes out the original sheets(3) abd skips original sheets(2), etc.
Gotcha! What was I thinkin'?

 

[JLGWhiz]

OK Jeff, after hashing things out with Dave, the following should do the
trick unless your new pages are interspersed. As long as they are either all
before, or all after the originals, one of the two versions below will work.

If the new sheets are inserted before the originals:

Application.DisplayAlerts = False
Dim Sh As Worksheet
'count new sheets
x = ThisWorkbook.Sheets.Count - myCount
For Each Sh In ThisWorkbook.Sheets
If Sh.Index > x Then
Sh.Delete
End If
Next
Application.DisplayAlerts = True

If the new sheets are inserted after the originals:

Application.DisplayAlerts = False
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Sheets
If Sh.Index <= myCount Then
Sh.Delete
End If
Next
Application.DisplayAlerts = True

 

[Dave Peterson]

Or you could just start with the rightmost original sheet and delete the sheets
to the left.

For i = myCount to 1 step -1
Sheets(i).Delete
Next i

 

[Sandy Mann]

If the new sheets are inserted after the originals:

Application.DisplayAlerts = False
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Sheets
If Sh.Index <= myCount Then
Sh.Delete
End If
Next
Application.DisplayAlerts = True

Dosen't that suffer from the same type problem as the original cpde? For
me it deletes all sheets up to the last and then errors out because it is
trying to delete the last sheet.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave Peterson]

Yep. You're correct.

Dosen't that suffer from the same type problem as the original cpde? For
me it deletes all sheets up to the last and then errors out because it is
trying to delete the last sheet.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk