VBA Assistance required

[bluenose76]

Hi,

I have a Workbook that contains 6 sheets. five of those sheets are updated regular and the 6th is there to consolodate.

the 5 sheets are updated using a Module that deletes the existing sheet and imports a new one that is more up to date. all sheets remaind named exactly the same at all times and in the same position (i.e. Sheet1 etc)

The layout and position of data is the same at all times but the data within does change.

at the bottom of each page there are some basic calculations and percentages.

what i need is for the sixth page to add up all the calculations & percentages to give me an overall figure.

I originally just pointed the cells to the respective pages an then added them alltogether and this worked! that is untill i ran my module again and it broke the reference and i end up with "#REF" in each field.

can anyone help me with what i am trying to achieve? or indeed tell me how i can stop excel from breakign the reference?

thankyou in advance for your help

Bev

 

[DCrake]

Because you deleted the sheets the ref was broken at that point, even if you recreate them is will not reinstate the reference. To overcome this at the botton of your module you may need to reset the formulas in the ref cells.

 

[bluenose76]

Thank you for your response and assistance.

I must say however you have now pushed the boundaries of my knowledge.

on my consolodation page I must have plus of 100 Formulas (all pointint to data on the first 5 sheets) and then some to calculate the overall totals.

How would i re-instate this at the bottom of my module?

any ideas would be great.

regards
Bev

 

[Brianwarnock]

I would look at having a Template of your worksheet in your folder, it need contain no data but all of the formulae in the summary sheet, you would then after copying across your new data sheet delete the existing summary sheet and copy in the sheet from the template, then you would need to alter all of the formula to remove the templates name.

Using the record macro feature it looks like this with only 2 data sheets

Windows("Book1template.xls").Activate
    Sheets("Sheet3").Copy After:=Workbooks("Book1.xls").Sheets(2)
    Range("A1").Select
    Cells.Replace What:="[Book1template.xls]", Replacement:="", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

play with this

Brian