Macro clears contents and my formula references

[moike]

I have a spreadsheet with a Master tab which can be changed and several adjacent tabs (we'll call them A,B,C,D, and E) which can not be changed by the user. The macro is pulling data from the Master tab and putting it on the tabs A through E depending on the different criteria of each line item. Everything is working as it should EXCEPT the user of this spreadsheet added a Summary tab to summarize the data from tabs A through E.

It is important to note that the macro is currently set to clear the contents of the entire tabs A-E before repopulating them with the most recent data from the Master tab. I have it set this way because I didn't want to risk having any leftover values in the various cells of tabs A - E after the macro is run and new data is fed into them.

Once she runs the macro to update all the adjacent tabs based on the changes to the Master tab; she loses the references she had on the Summary tab because of course the cells that are referenced were just deleted.

I can add code to input the formulas after the cells are cleared and then repopulated but I would like to allow her some freedom to make changes to the format of this tab without me having to change the VB code every time. I have tried a few ideas to make this work and the only thing so far is if you copy her Summary tab to another worksheet and close it...you can copy it back to the Summary tab after the macro runs and the references in the formulas are working as they should.

Is there another alternative???

[Bryan Hessey]

Can you use a helper sheet? - within your macro, create a new sheet 'Values' and copy - paste special = values from the master sheet and use that data to re-populate, then remove the 'Values' sheet? - I presume that you are setting cells to blank rather than deleting rows.

If this does not work, can the

Please Login or Register  to view this content.

provide the interruption you are looking for?

---

I have a spreadsheet with a Master tab which can be changed and several adjacent tabs (we'll call them A,B,C,D, and E) which can not be changed by the user. The macro is pulling data from the Master tab and putting it on the tabs A through E depending on the different criteria of each line item. Everything is working as it should EXCEPT the user of this spreadsheet added a Summary tab to summarize the data from tabs A through E.

It is important to note that the macro is currently set to clear the contents of the entire tabs A-E before repopulating them with the most recent data from the Master tab. I have it set this way because I didn't want to risk having any leftover values in the various cells of tabs A - E after the macro is run and new data is fed into them.

Once she runs the macro to update all the adjacent tabs based on the changes to the Master tab; she loses the references she had on the Summary tab because of course the cells that are referenced were just deleted.

I can add code to input the formulas after the cells are cleared and then repopulated but I would like to allow her some freedom to make changes to the format of this tab without me having to change the VB code every time. I have tried a few ideas to make this work and the only thing so far is if you copy her Summary tab to another worksheet and close it...you can copy it back to the Summary tab after the macro runs and the references in the formulas are working as they should.

Is there another alternative???

[Tristan]

Hi,

Just so we're clear what is the problem here?
Is it
1) The cells in tabs A-D are deleted and in doing so her formulas lose their references. If this is the case then changing your code from ".Delete" to ".ClearContents" thus clearing the contents of the cells rather than removing them entirely should accomplish your safeguarding practice whilst maintaining her formuals.

or

2) She'd like to keep the previous summary data (for archiving or whatever) and create a new summary from the updated data. The problem here being that at the moment her values automatically update with te new data and she loses what she had previously.

or

3) other?????

Tris

[moike]

First off, here's the cliff notes from my original post to hopefully make for a less painful read:

Worksheet contains 7 tabs (Master, A,B,C,D,E, and Summary). Master tab is the only tab I want the user to have the freedom to change and this tab has all the data. Tabs A,B,C,D, and E are different financial views and are populated with certain rows and values from the Master tab if criteria for that tab is met. The macro clears the contents of tabs A,B,C,D, and E every time it is run and then repopulates those tabs with the most current data from the Master Tab to avoid having any lingering data from before.

The owner of the spreadsheet added the Summary tab to summarize the data on tabs A,B,C,D, and E and this Summary tabs contains several references to values on those tabs. The problem is that when she runs the macro and all the contents are deleted from tabs A-E; the references are either not valid (#REF!) or they reference different cells than they did before the macro was run.

I sincerely appreciate the responses and I am testing both.

[moike]

Can you use a helper sheet? - within your macro, create a new sheet 'Values' and copy - paste special = values from the master sheet and use that data to re-populate, then remove the 'Values' sheet? - I presume that you are setting cells to blank rather than deleting rows.

If this does not work, can the

Please Login or Register  to view this content.

provide the interruption you are looking for?

---

I tried the "Worksheets(1).EnableCalculation = False" code on the Summary tab. This reduces the #REF! errors but the fields on the Summary tab that reference the tabs A-E have different references now so the result on the Summary tab is of course different. I need these fields on the Summary tab to reference specific locations on tabs A-E and that shouldn't change after the macro is run.

[moike]

Also, regarding the helper sheet to house the formulas while the macro runs: I have tried this manually by copying and paste - special to another worksheet before the macro runs but the references change if that worksheet is open during the macro. The only way I have found to get around this is to copy paste -special to another sheet, save it, and then close the copied version. I can then run the macro on the original and then reopen the copied version and paste the data back into the original summary tab.

I assume there is a way around this using VB commands but I'm quite the noob and haven't found it yet.

[Bryan Hessey]

Hi,

Did you have any joy with Tristan's second point of using 'Clear contents' instead of 'delete'?

If not, can you post your Macro here?
---

Also, regarding the helper sheet to house the formulas while the macro runs: I have tried this manually by copying and paste - special to another worksheet before the macro runs but the references change if that worksheet is open during the macro. The only way I have found to get around this is to copy paste -special to another sheet, save it, and then close the copied version. I can then run the macro on the original and then reopen the copied version and paste the data back into the original summary tab.

I assume there is a way around this using VB commands but I'm quite the noob and haven't found it yet.

[moike]

Yes, unfortunately I already have it set to clear contents as opposed to delete. The macro is VERY long and I don't envy anyone who would have to sift through my VB code.

The goal of my original post was to find out if this scenario would work in a macro: I want to have fields on the summary tab which will reference specific cell locations on other tabs and the contents of those other tabs will be cleared entirely before being repopulated with data from the Master tab. If the cell references on the Summary tab really should still be able to reference the same cell locations on the other tabs after this has occurred then my problem must be elsewhere. I will go through the code and make sure there isn't some other reason I am not able to keep the same cell references after running the macro.

[Bryan Hessey]

Hi,

Yes, the idea of 'clear contents' (same as pressing 'Delete' in manual mode) should leave the formulae ok, with just #N/A etc for results but should not corrupt formula, only removal of the referenced cell should do that.

Good luck searching your code, if it fails just post it, there are some helpers here that appear to be well up on the VB side that could pick your problem effortlessly. At worst the problem will just get ignored (with nothing lost, 'face' doesn't count)

Good luck with it, and thanks for your responses.

---

Yes, unfortunately I already have it set to clear contents as opposed to delete. The macro is VERY long and I don't envy anyone who would have to sift through my VB code.

The goal of my original post was to find out if this scenario would work in a macro: I want to have fields on the summary tab which will reference specific cell locations on other tabs and the contents of those other tabs will be cleared entirely before being repopulated with data from the Master tab. If the cell references on the Summary tab really should still be able to reference the same cell locations on the other tabs after this has occurred then my problem must be elsewhere. I will go through the code and make sure there isn't some other reason I am not able to keep the same cell references after running the macro.

[moike]

As usual, the problem was a user error because the macro was inserting rows and arranging the location of certain columns. This is the cause of my references to the cells being broken. I completely changed the macro to leave the location of the cells being referenced unchanged.

Thanks everyone for the input.