Reference worksheet by partial name ?? wildcard

Hello..

I have several pivot tables I've created from code derived from a "master" 2
week work schedule. Each pivot table summarizes the daily job schedules and
resources. I want to be able to delete the pivot table worksheets whenever
the schedule is updated in order to recreate each pivot table summary
reflecting the new schedule.

Each pivot table worksheet is named by a date followed by "summary" i.e. Oct
21 summary. I would like to be able to delete each worksheet having the
word "summary". So far I've been able to do this by using worksheets(index)
if the worksheet is appended after the master schedule. But if a user adds
another worksheet in the same manner it could get deleted in error.

I appreciate any help you can provide..thanks.

Think I have a passable workaround...I tagged each pivot table with a title
which I reference as an IF condition before deleting the worksheet...but if
anyone has a better approach I'm open to suggestions.


"Dale" <dale@nospam.com> wrote in message
news:e07OfumuGHA.2036@TK2MSFTNGP05.phx.gbl...
> Hello..
>
> I have several pivot tables I've created from code derived from a "master"
> 2 week work schedule. Each pivot table summarizes the daily job schedules
> and resources. I want to be able to delete the pivot table worksheets
> whenever the schedule is updated in order to recreate each pivot table
> summary reflecting the new schedule.
>
> Each pivot table worksheet is named by a date followed by "summary" i.e.
> Oct 21 summary. I would like to be able to delete each worksheet having
> the word "summary". So far I've been able to do this by using
> worksheets(index) if the worksheet is appended after the master schedule.
> But if a user adds another worksheet in the same manner it could get
> deleted in error.
>
> I appreciate any help you can provide..thanks.
>

This will delete any worksheet that ends with: number, space, Summary...
Dim WS As Excel.Worksheet
For Each WS In Worksheets
If WS.Name Like "*# Summary" Then
WS.Delete
End If
Next
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Dale"
<dale@nospam.com>
wrote in message
Hello..
I have several pivot tables I've created from code derived from a "master" 2
week work schedule. Each pivot table summarizes the daily job schedules and
resources. I want to be able to delete the pivot table worksheets whenever
the schedule is updated in order to recreate each pivot table summary
reflecting the new schedule.

Each pivot table worksheet is named by a date followed by "summary" i.e. Oct
21 summary. I would like to be able to delete each worksheet having the
word "summary". So far I've been able to do this by using worksheets(index)
if the worksheet is appended after the master schedule. But if a user adds
another worksheet in the same manner it could get deleted in error.
I appreciate any help you can provide..thanks.

Excellent...I should have known that...

"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message
news:%23DsswenuGHA.3500@TK2MSFTNGP06.phx.gbl...
> This will delete any worksheet that ends with: number, space, Summary...
> Dim WS As Excel.Worksheet
> For Each WS In Worksheets
> If WS.Name Like "*# Summary" Then
> WS.Delete
> End If
> Next
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> "Dale"
> <dale@nospam.com>
> wrote in message
> Hello..
> I have several pivot tables I've created from code derived from a "master"
> 2
> week work schedule. Each pivot table summarizes the daily job schedules
> and
> resources. I want to be able to delete the pivot table worksheets
> whenever
> the schedule is updated in order to recreate each pivot table summary
> reflecting the new schedule.
>
> Each pivot table worksheet is named by a date followed by "summary" i.e.
> Oct
> 21 summary. I would like to be able to delete each worksheet having the
> word "summary". So far I've been able to do this by using
> worksheets(index)
> if the worksheet is appended after the master schedule. But if a user
> adds
> another worksheet in the same manner it could get deleted in error.
> I appreciate any help you can provide..thanks.
>
>