Hello Bill,

I generally do not use worksheet functions in the macros I
write. This particular macros did have a VLookup worksheet
function on one of the pages that was being deleted at the
end of the macro - in fact it referred to one of the other
sheets that was also deleted at the end of the macro.

But, for the sake of science I included code in the macro
that copied the results of the VLookup and pasted only the
values back on top of the cells that employed the VLookup,
so as to get rid of all worksheet functions before the end
of the macro and the sheet deletes.

Even without any worksheet functions, if the three sheet
deletes(that sounds lyrical) were in the main sub where
the SaveAs also occurred shortly after, then there
were "changes" that still needed to be saved when the
Excel file was closed - closed after the macro had just
saved the file during execution of code.

I am not sure what effect calling a Private Sub has on
Excel but, again, when I put the three sheet deletes in a
separate Private Sub, Excel understands that those sheets
have been deleted and Excel is okay with the programmatic
SaveAs and there are no "changes" that need to be saved
when the file is closed later.

Am I the only one that has experienced this? Can you
reproduce this in a macro of your own? If so, it seems
that this is something that is a disadvantage for coders
and users of Excel macros and could(should?) be changed by
Microsoft. Please share your further thoughts on this.
Thank you.

-Izar Arcturus


-----Original Message-----
Izar Arcturus wrote:
It's as if the deleting of these sheets does not finish
before the SaveAs has finished or even the macro has
finished.


Or it could be that the deleted sheets contain worksheet
formulas whose
values change after the SaveAs. By deleting the sheets
you have
deleted the formulas which were marking the workbook as
not saved.

Just a thought.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

.