I suspect that the following bit of code is causing problems - not
causing an arror - just stopping the macro part way through without an
error. Does anyone know any other ways of coding the follow, I suspect
it is the for Each loop causing the problems:

Any help appreciated
Jason

'====================================
For Each mySheet In myStorageBook.Worksheets

'check to see if the storage sheet is being used
'if it isn't then delete it
If mySheet.Range("C2") = "Empty" Then
Application.DisplayAlerts = False
mySheet.Delete
Application.DisplayAlerts = True
Else
If mySheet.Name <> "Input" And mySheet.Name <> "Summary"
Then
mySheet.Range("D:G").EntireColumn.AutoFit
End If
End If
Next
'====================================

Is this a macro or a worksheet/workbook event ?


If this post helps click Yes
---------------
Jacob Skaria


"WhytheQ" wrote:

> I suspect that the following bit of code is causing problems - not
> causing an arror - just stopping the macro part way through without an
> error. Does anyone know any other ways of coding the follow, I suspect
> it is the for Each loop causing the problems:
>
> Any help appreciated
> Jason
>
> '====================================
> For Each mySheet In myStorageBook.Worksheets
>
> 'check to see if the storage sheet is being used
> 'if it isn't then delete it
> If mySheet.Range("C2") = "Empty" Then
> Application.DisplayAlerts = False
> mySheet.Delete
> Application.DisplayAlerts = True
> Else
> If mySheet.Name <> "Input" And mySheet.Name <> "Summary"
> Then
> mySheet.Range("D:G").EntireColumn.AutoFit
> End If
> End If
> Next
> '====================================
>

I suspect this could be your problem
If mySheet.Range("C2") = "Empty" Then
Maybe assuming you have a set mystoragebook=something

For Each ws In myStorageBook.Worksheets
If ws.Name <> "Input" And ws.Name <> "Summary then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
else ws.columns("D:G").AutoFit
end if



--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"WhytheQ" <(E-Mail Removed)> wrote in message
news:a2bf0778-6c01-4c0b-8648-(E-Mail Removed)...
>I suspect that the following bit of code is causing problems - not
> causing an arror - just stopping the macro part way through without an
> error. Does anyone know any other ways of coding the follow, I suspect
> it is the for Each loop causing the problems:
>
> Any help appreciated
> Jason
>
> '====================================
> For Each mySheet In myStorageBook.Worksheets
>
> 'check to see if the storage sheet is being used
> 'if it isn't then delete it
> If mySheet.Range("C2") = "Empty" Then
> Application.DisplayAlerts = False
> mySheet.Delete
> Application.DisplayAlerts = True
> Else
> If mySheet.Name <> "Input" And mySheet.Name <> "Summary"
> Then
> mySheet.Range("D:G").EntireColumn.AutoFit
> End If
> End If
> Next
> '====================================

I left out the most important part.....
If Len(Application.Trim(ActiveCell)) < 1 Then MsgBox "hi"
=========
For Each ws In myStorageBook.Worksheets
If ws.Name <> "Input" And ws.Name <> "Summary then
If Len(Application.Trim(ActiveCell)) < 1 Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
else ws.columns("D:G").AutoFit
end if
end if
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I suspect this could be your problem
> If mySheet.Range("C2") = "Empty" Then
> Maybe assuming you have a set mystoragebook=something
>
> For Each ws In myStorageBook.Worksheets
> If ws.Name <> "Input" And ws.Name <> "Summary then
> Application.DisplayAlerts = False
> ws.Delete
> Application.DisplayAlerts = True
> else ws.columns("D:G").AutoFit
> end if
>
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "WhytheQ" <(E-Mail Removed)> wrote in message
> news:a2bf0778-6c01-4c0b-8648-(E-Mail Removed)...
>>I suspect that the following bit of code is causing problems - not
>> causing an arror - just stopping the macro part way through without an
>> error. Does anyone know any other ways of coding the follow, I suspect
>> it is the for Each loop causing the problems:
>>
>> Any help appreciated
>> Jason
>>
>> '====================================
>> For Each mySheet In myStorageBook.Worksheets
>>
>> 'check to see if the storage sheet is being used
>> 'if it isn't then delete it
>> If mySheet.Range("C2") = "Empty" Then
>> Application.DisplayAlerts = False
>> mySheet.Delete
>> Application.DisplayAlerts = True
>> Else
>> If mySheet.Name <> "Input" And mySheet.Name <> "Summary"
>> Then
>> mySheet.Range("D:G").EntireColumn.AutoFit
>> End If
>> End If
>> Next
>> '====================================
>

NOT a good day
If Len(Application.Trim(ws.range("c2"))) < 1 Then

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I left out the most important part.....
> If Len(Application.Trim(ActiveCell)) < 1 Then MsgBox "hi"
> =========
> For Each ws In myStorageBook.Worksheets
> If ws.Name <> "Input" And ws.Name <> "Summary then
> If Len(Application.Trim(ActiveCell)) < 1 Then
> Application.DisplayAlerts = False
> ws.Delete
> Application.DisplayAlerts = True
> else ws.columns("D:G").AutoFit
> end if
> end if
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>I suspect this could be your problem
>> If mySheet.Range("C2") = "Empty" Then
>> Maybe assuming you have a set mystoragebook=something
>>
>> For Each ws In myStorageBook.Worksheets
>> If ws.Name <> "Input" And ws.Name <> "Summary then
>> Application.DisplayAlerts = False
>> ws.Delete
>> Application.DisplayAlerts = True
>> else ws.columns("D:G").AutoFit
>> end if
>>
>>
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "WhytheQ" <(E-Mail Removed)> wrote in message
>> news:a2bf0778-6c01-4c0b-8648-(E-Mail Removed)...
>>>I suspect that the following bit of code is causing problems - not
>>> causing an arror - just stopping the macro part way through without an
>>> error. Does anyone know any other ways of coding the follow, I suspect
>>> it is the for Each loop causing the problems:
>>>
>>> Any help appreciated
>>> Jason
>>>
>>> '====================================
>>> For Each mySheet In myStorageBook.Worksheets
>>>
>>> 'check to see if the storage sheet is being used
>>> 'if it isn't then delete it
>>> If mySheet.Range("C2") = "Empty" Then
>>> Application.DisplayAlerts = False
>>> mySheet.Delete
>>> Application.DisplayAlerts = True
>>> Else
>>> If mySheet.Name <> "Input" And mySheet.Name <> "Summary"
>>> Then
>>> mySheet.Range("D:G").EntireColumn.AutoFit
>>> End If
>>> End If
>>> Next
>>> '====================================
>>
>

On 17 Aug, 21:36, "Don Guillett" <dguille...@austin.rr.com> wrote:
> NOT a good day
> *If Len(Application.Trim(ws.range("c2"))) < 1 Then
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"Don Guillett" <dguille...@austin.rr.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> >I left out the most important part.....
> > If Len(Application.Trim(ActiveCell)) < 1 Then MsgBox "hi"
> > =========
> > For Each ws In myStorageBook.Worksheets
> > *If ws.Name <> "Input" And ws.Name <> "Summary then
> > *If Len(Application.Trim(ActiveCell)) < 1 Then
> > * * Application.DisplayAlerts = False
> > * * * ws.Delete
> > * * Application.DisplayAlerts = True
> > else ws.columns("D:G").AutoFit
> > end if
> > end if
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > dguille...@austin.rr.com
> > "Don Guillett" <dguille...@austin.rr.com> wrote in message
> >news:(E-Mail Removed)...
> >>I suspect this could be your problem
> >> If mySheet.Range("C2") = "Empty" Then
> >> Maybe assuming you have a set mystoragebook=something
>
> >> For Each ws In myStorageBook.Worksheets
> >> *If ws.Name <> "Input" And ws.Name <> "Summary then
> >> * * Application.DisplayAlerts = False
> >> * * * ws.Delete
> >> * * Application.DisplayAlerts = True
> >> else ws.columns("D:G").AutoFit
> >> end if
>
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> dguille...@austin.rr.com
> >> "WhytheQ" <whyt...@gmail.com> wrote in message
> >>news:a2bf0778-6c01-4c0b-8648-(E-Mail Removed)....
> >>>I suspect that the following bit of code is causing problems - not
> >>> causing an arror - just stopping the macro part way through without an
> >>> error. Does anyone know any other ways of coding the follow, I suspect
> >>> it is the for Each loop causing the problems:
>
> >>> Any help appreciated
> >>> Jason
>
> >>> '====================================
> >>> For Each mySheet In myStorageBook.Worksheets
>
> >>> * * * * * *'check to see if the storage sheet is being used
> >>> * * * * * *'if it isn't then delete it
> >>> * * *If mySheet.Range("C2") = "Empty" Then
> >>> * * * * * *Application.DisplayAlerts = False
> >>> * * * * * * * * *mySheet.Delete
> >>> * * * * * *Application.DisplayAlerts = True
> >>> * * *Else
> >>> * * * * * *If mySheet.Name <> "Input" And mySheet.Name <>"Summary"
> >>> Then
> >>> * * * * * * * * *mySheet.Range("D:G").EntireColumn.AutoFit
> >>> * * * * * *End If
> >>> * * *End If
> >>> Next
> >>> '====================================- Hide quoted text -
>
> - Show quoted text -


Hello Don
- thanks for all the help so far.

Bit of a misunderstanding (quite understandable) - in Range("C2") of
each of the sheets there is a formula using the IF function and one of
the options is the string "Empty" ....

=IF(ISNA(MATCH(B4,Input!C6:AZ6,0)),"Empty",MATCH(B4,Input!C6:AZ6,0))

Anyway I've moved the nested IFs around as you have and also changed
been more explicit with Range("C2") ... Range("C2").Value ; I know
this is the default property but maybe it'll help.

Will report back after testing

Thanks again
Jason.