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.

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


Replace @mailinator.com with @tiscali.co.uk


"Jeff Kelly" wrote in message
...
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.

If you dont want to rename the sheets you could send all the names of
the sheets as they are when you open the workbook to an array or simply
to a list in a worksheet then delete all in the array or list after
adding your new sheets, however Sandy has given you a nice simple macro
to follow.


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27225

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.

"Jeff Kelly" wrote:

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.

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.)

JLGWhiz wrote:

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.

"Jeff Kelly" wrote:

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.




--

Dave Peterson

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


+++++++++++++++++++++++
"Dave Peterson" wrote in message
...
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.)

JLGWhiz wrote:

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.

"Jeff Kelly" wrote:

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.




--

Dave Peterson

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" wrote:

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.)

JLGWhiz wrote:

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.

"Jeff Kelly" wrote:

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.




--

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:
For i = 1 To myCount
Sheets(i).Delete
Next

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 wrote:

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" wrote:

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.)

JLGWhiz wrote:

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.

"Jeff Kelly" wrote:

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.




--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote:

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:
For i = 1 To myCount
Sheets(i).Delete
Next

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 wrote:

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" wrote:

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.)

JLGWhiz wrote:

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.

"Jeff Kelly" wrote:

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.




--

Dave Peterson


--

Dave Peterson

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

"Jeff Kelly" wrote:

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.

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



JLGWhiz wrote:

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

"Jeff Kelly" wrote:

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.




--

Dave Peterson

JLGWhiz" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"JLGWhiz" wrote in message
...
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

"Jeff Kelly" wrote:

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.

Yep. You're correct.

Sandy Mann wrote:

JLGWhiz" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk

"JLGWhiz" wrote in message
...
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

"Jeff Kelly" wrote:

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.





--

Dave Peterson