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

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Jeff Kelly" <(E-Mail Removed)> wrote in message
news:49172c37$0$24420$(E-Mail Removed)...
>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 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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