Excel losing references

[ls62]

Hi,

I have Excel 97 workbook that has several sheets. The first sheetis a summary of data on the other sheets. I have a macro that I occasionally run that deletes the 'other' sheets and recreates them from csv files. The problem is once this is done the 'summary' sheet loses reference to the cells on the 'other' sheets.

For example if on the 'summary' sheet I had a cell with:
='style1'!b69

If my vba macro deletes style1 sheet and recreates it when I go back and look at the cell in the 'summary' sheet it has:

=#ref!b69

Is there any way to keep excel from losing these references?

Anyone have a alternate what of doing this?

Thanks. LEE

 

[RamziSaab]

yes go at the begining of the code paste this

Application.Calculation = xlmanual

and at the end paste this

Application.Calculation = xlAutomatic

tell me if you need more help

 

[ls62]

Hi,

Actually what I'm doing is manually deleting the 'other' sheets before I run the macro. My macro then sees the sheet doesn't exist and creates a new one. Problem is that as soon as I delete the 'other' sheets the references are lost on the summary sheet before I even get a chance to run the macro. I've gone into the tools, options, calulate tab and checked 'manual' calc, unchecked 'recalc before save', and unchecked 'update remote references' ... still loses the reference.

LEE

 

[GlennUK]

You could change the references to text before you do anything else, and afterwards convert them back again.

I use Edit/Replace and replace = with # to convert formulae to text ( doing Replace All ). Afterwards do Edit/Replace, and replace # with =, and you get your formulae back again.

Cheers, Glenn.

 

[ls62]

Glenn,

Ok, sound good... I'll give that a try. Meanwhile I did get it to work by creating a 'new' workbook, copying he 'summary' sheet to the new workbook , saving/closing it. Then took my existing workbook, deleted the summary sheet, and detailed 'other' sheets, Ran the macro to rebuild 'other' sheets. When this was all done I opened the saved workbook from earlier and recopied the 'summary' sheet back to my book in its original place.

Seem to work fine that way too, your method may be easier.

thanks.
LEE

 

[RamziSaab]

or just go to tools-->options

and put calculation on manual...change it back to automatic when u want to update

 

[GlennUK]

Hi RamziSaab,

sorry to disagree with you, but that is rubbish. Have you actually tried this yourself? Altering the calculation mode will not stop links from changing to #REF! errors.

Glenn.

 

[RamziSaab]

i simple 'that does not work would have done just great there!!'


here is another way you can do it:

Open the workbook with the New sheet (the one with the new info) and Select every cell in hte new sheet, and
paste everything into the old info sheet this will allow u not to mess with the formulas and u can totally automate the process...

 

[GlennUK]

Well RamziSaab,

sorry if I offended, but you posted a solution that could have led to Lee wasting a lot of time trying to get it to work. You should really be more careful when posting solutions.

Glenn.

 

[RamziSaab]

yes but thankfully ur careful and watchful eye was there and it didnt

 

[ls62]

RamziSaab & Glenn,

No problem with any of your suggestions, I had already tried the tools,options, calculate method before posting, so I knew that wouldn't work and posted that fact in my previous post. What seems to have worked is copies the sheet to a new workbook, saving and closeing it and then makin the mods to the existing sheet. Once everything was rebuilt I reopened the saved book with the summary sheet , recopied it back and it retainted the references.

Important part is that you have to SAVE AND CLOSE the book that you copy the sheet to so that excel doesn't try and resolve the links.

Thanks for your help.
LEE