here is my awesome code:

Option Private Module
Option Compare Binary
Option Explicit
Option Base 0

Public Const strDeleteSheet = "delSht"

Private Sub deleteSheets()

Dim xlSheet As Excel.Worksheet

Application.DisplayAlerts = False

For Each xlSheet In Application.ActiveWorkbook.Worksheets
If (xlSheet.CodeName Like strDeleteSheet & "*") Then
wkbActive.Worksheets(xlSheet.Name).Delete
End If
Next xlSheet

Application.DisplayAlerts = True

Set xlSheet = Nothing

End Sub

A not trappable error occurs: xlSheet.Name = <Automation error>. The code
does not stop processing. I know this is due to changing the items in
ActiveWorkbook.Worksheets.

This error resets globally declared variables to empty/false/etc...

Any suggestions to delete sheets without resetting global variables?

Thank you.
rogge

First, instead of:
wkbActive.Worksheets(xlSheet.Name).Delete

You may want to use:
Application.ActiveWorkbook.Worksheets(xlSheet.Name).Delete

Actually, I'd use:
xlSheet.Delete

In the code you posted, there's nothing that declares or sets wkbActive.

rogge wrote:
>
> here is my awesome code:
>
> Option Private Module
> Option Compare Binary
> Option Explicit
> Option Base 0
>
> Public Const strDeleteSheet = "delSht"
>
> Private Sub deleteSheets()
>
> Dim xlSheet As Excel.Worksheet
>
> Application.DisplayAlerts = False
>
> For Each xlSheet In Application.ActiveWorkbook.Worksheets
> If (xlSheet.CodeName Like strDeleteSheet & "*") Then
> wkbActive.Worksheets(xlSheet.Name).Delete
> End If
> Next xlSheet
>
> Application.DisplayAlerts = True
>
> Set xlSheet = Nothing
>
> End Sub
>
> A not trappable error occurs: xlSheet.Name = <Automation error>. The code
> does not stop processing. I know this is due to changing the items in
> ActiveWorkbook.Worksheets.
>
> This error resets globally declared variables to empty/false/etc...
>
> Any suggestions to delete sheets without resetting global variables?
>
> Thank you.
> rogge

--

Dave Peterson

wkbActive is declared elsewhere...
I do like xlSheet.Delete...

but i had one of our excel experts look at the code, etc.. the public
variables are being reset by some other code... and the "watch" is not
finding when the value changes.

"Dave Peterson" wrote:

> First, instead of:
> wkbActive.Worksheets(xlSheet.Name).Delete
>
> You may want to use:
> Application.ActiveWorkbook.Worksheets(xlSheet.Name).Delete
>
> Actually, I'd use:
> xlSheet.Delete
>
> In the code you posted, there's nothing that declares or sets wkbActive.
>
> rogge wrote:
> >
> > here is my awesome code:
> >
> > Option Private Module
> > Option Compare Binary
> > Option Explicit
> > Option Base 0
> >
> > Public Const strDeleteSheet = "delSht"
> >
> > Private Sub deleteSheets()
> >
> > Dim xlSheet As Excel.Worksheet
> >
> > Application.DisplayAlerts = False
> >
> > For Each xlSheet In Application.ActiveWorkbook.Worksheets
> > If (xlSheet.CodeName Like strDeleteSheet & "*") Then
> > wkbActive.Worksheets(xlSheet.Name).Delete
> > End If
> > Next xlSheet
> >
> > Application.DisplayAlerts = True
> >
> > Set xlSheet = Nothing
> >
> > End Sub
> >
> > A not trappable error occurs: xlSheet.Name = <Automation error>. The code
> > does not stop processing. I know this is due to changing the items in
> > ActiveWorkbook.Worksheets.
> >
> > This error resets globally declared variables to empty/false/etc...
> >
> > Any suggestions to delete sheets without resetting global variables?
> >
> > Thank you.
> > rogge
>
> --
>
> Dave Peterson
>

What kind of code are you using that resets that variable?

Maybe it's better to fix that.

======
But for this situation, I would be surprised (but it's possible), that you
really meant the activeworkbook:

> > > For Each xlSheet In Application.ActiveWorkbook.Worksheets
> > > If (xlSheet.CodeName Like strDeleteSheet & "*") Then
> > > wkbActive.Worksheets(xlSheet.Name).Delete
> > > End If
> > > Next xlSheet

It would be pretty weird (but not unheard of) to loop through one workbook to
delete stuff in another.

rogge wrote:
>
> wkbActive is declared elsewhere...
> I do like xlSheet.Delete...
>
> but i had one of our excel experts look at the code, etc.. the public
> variables are being reset by some other code... and the "watch" is not
> finding when the value changes.
>
> "Dave Peterson" wrote:
>
> > First, instead of:
> > wkbActive.Worksheets(xlSheet.Name).Delete
> >
> > You may want to use:
> > Application.ActiveWorkbook.Worksheets(xlSheet.Name).Delete
> >
> > Actually, I'd use:
> > xlSheet.Delete
> >
> > In the code you posted, there's nothing that declares or sets wkbActive.
> >
> > rogge wrote:
> > >
> > > here is my awesome code:
> > >
> > > Option Private Module
> > > Option Compare Binary
> > > Option Explicit
> > > Option Base 0
> > >
> > > Public Const strDeleteSheet = "delSht"
> > >
> > > Private Sub deleteSheets()
> > >
> > > Dim xlSheet As Excel.Worksheet
> > >
> > > Application.DisplayAlerts = False
> > >
> > > For Each xlSheet In Application.ActiveWorkbook.Worksheets
> > > If (xlSheet.CodeName Like strDeleteSheet & "*") Then
> > > wkbActive.Worksheets(xlSheet.Name).Delete
> > > End If
> > > Next xlSheet
> > >
> > > Application.DisplayAlerts = True
> > >
> > > Set xlSheet = Nothing
> > >
> > > End Sub
> > >
> > > A not trappable error occurs: xlSheet.Name = <Automation error>. The code
> > > does not stop processing. I know this is due to changing the items in
> > > ActiveWorkbook.Worksheets.
> > >
> > > This error resets globally declared variables to empty/false/etc...
> > >
> > > Any suggestions to delete sheets without resetting global variables?
> > >
> > > Thank you.
> > > rogge
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

All of the code is affecting items in the activeworkbook. I don't use code
in this workbook to change objects in another workbook.

Only callbacks (procedures executed from the ribbon) set this boolean
variable.

I am going to ask this resetting question in a separate thread because i
have found that ending the code execution resets the boolean.... please
search for "rogge"

thanks for your help.

"Dave Peterson" wrote:

> What kind of code are you using that resets that variable?
>
> Maybe it's better to fix that.
>
> ======
> But for this situation, I would be surprised (but it's possible), that you
> really meant the activeworkbook:
>

I don't use the End command (not End sub and not end if ...) in code. It ends
the running macro, but doesn't do it gracefully (as you've seen).

rogge wrote:
>
> All of the code is affecting items in the activeworkbook. I don't use code
> in this workbook to change objects in another workbook.
>
> Only callbacks (procedures executed from the ribbon) set this boolean
> variable.
>
> I am going to ask this resetting question in a separate thread because i
> have found that ending the code execution resets the boolean.... please
> search for "rogge"
>
> thanks for your help.
>
> "Dave Peterson" wrote:
>
> > What kind of code are you using that resets that variable?
> >
> > Maybe it's better to fix that.
> >
> > ======
> > But for this situation, I would be surprised (but it's possible), that you
> > really meant the activeworkbook:
> >

--

Dave Peterson