I have a workbook with about 15 sheets, and a bit of VB code running
some pivot tables. The whole workbook is rather large (23mb) as this
analyzes about 30k records.

The problem happens when I try to "Save As". While the book saves fine,
the "save as" function gives me many runtime errors on various bits of
code. When I click debug on the errors, it always highlights a line
which refers to a 'selection'(copy, paste, etc.). The lines which cause
the errors vary depending on which sheet is active at the time- ie, the
code behind a certain page will not throw any errors if that sheet is
active at the time, but the other pages will. I have taken out all
activesheet and activecell references, but these selection commands are
giving me trouble. It's as if each page's code is fully running
everytime I click "Save As".

Normally this would not be a problem, but this is a template for other
users and they will all be needing to "Save As". Any ideas?

clean up the code so it doesn't do any selecting.

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
I have a workbook with about 15 sheets, and a bit of VB code running
some pivot tables. The whole workbook is rather large (23mb) as this
analyzes about 30k records.

The problem happens when I try to "Save As". While the book saves fine,
the "save as" function gives me many runtime errors on various bits of
code. When I click debug on the errors, it always highlights a line
which refers to a 'selection'(copy, paste, etc.). The lines which cause
the errors vary depending on which sheet is active at the time- ie, the
code behind a certain page will not throw any errors if that sheet is
active at the time, but the other pages will. I have taken out all
activesheet and activecell references, but these selection commands are
giving me trouble. It's as if each page's code is fully running
everytime I click "Save As".

Normally this would not be a problem, but this is a template for other
users and they will all be needing to "Save As". Any ideas?

Even if I got rid of all the selecting - the problem still exists that
whenever I "save as" all the code runs. Each and every function, which
takes a while. There is nothing in ThisWorkbook, no events BeforeSave,
no modules at all, just code behind the sheets. Again what is peculiar
is this only happens when "saved as", saving works just fine. I even
tried disabling events, which didn't work.

The problem may be related to the fact that you are doing a Save-As on the
workbook containing the code. Not sure - but excel may not like that
scenerio.

I know that save works OK when the code is in the workbook being saved.

--
steveB

Remove "AYN" from email to respond
wrote in message
ups.com...
Even if I got rid of all the selecting - the problem still exists that
whenever I "save as" all the code runs. Each and every function, which
takes a while. There is nothing in ThisWorkbook, no events BeforeSave,
no modules at all, just code behind the sheets. Again what is peculiar
is this only happens when "saved as", saving works just fine. I even
tried disabling events, which didn't work.

I think Excel calculates before saving, and I think code behind sheets is
less controllable than in modules so its probably running as part of the
recalc. Are you using the calc event?
If you get rid of the selecting then you should not get runtime errors that
depend on specific sheets being active and your code should run significantly
faster.

cheers
simon
" wrote:

Even if I got rid of all the selecting - the problem still exists that
whenever I "save as" all the code runs. Each and every function, which
takes a while. There is nothing in ThisWorkbook, no events BeforeSave,
no modules at all, just code behind the sheets. Again what is peculiar
is this only happens when "saved as", saving works just fine. I even
tried disabling events, which didn't work.

Well, I figured it out...kind of. For some reason, excel was
triggering the combo box events on the save-as. I have no idea why,
but it made all of them run on whichever sheet was active. I got rid
of all my combo boxes and am using a cell with data validation, and now
the save-as works fine. Thanks for your suggestions.