Sheet name changing dilemma

[Danielle]

So I have a workbook with 9 sheets of data and 9 more sheets of charts and
graphs that feed off of that data. The nine sheets of data rotate each week.
Week 1 of data rolls off and a new week 9 of data rolls on. I'm working on
do this via a macro. Here's the problem: the two options I know of are to
copy and paste all of the data from each sheet to next sheet (i.e. copy week
2 data and paste on week 1 sheet, copy week 3 and pasted on week 2, etc.) OR
I could have the macro just rename the sheets. For obvious reasons that would
be the preferred method. However, it screws my formulas up because when I
rename Week 2 to Week 1 and so forth, my formulas change with them. Such a
handy little function of Excel. Except it's screwing up my workbook!!! I need
the formulas to remain the same!

Is there some code that will allow me to bypass or turn off the sheet name
updates until my macro is finished running?

Any suggestions would be most appreciated!

 

[Wouter HM]

Hi Danielle,

In Excel 2003 I created this macro.
I assume you have alternating sheets and graphs.
Since you do not explain where the new Sheet9 and Graph9 are coming
from I reuse the original Sheet1 and Graph1.
At the end of this macro you have to add some code to clear the data
from Sheet9.

Sub CycleSheets()
Dim i As Integer
'
i = ThisWorkbook.Sheets.Count
Sheets(Array("Chart1", "Sheet1")).Select
Sheets(Array("Chart1", "Sheet1")).Move _
after:=Sheets(i)
Sheets("Sheet1").Name = "Sheet10"
Sheets("Chart1").Name = "Chart10"
Sheets("Sheet2").Name = "Sheet1"
Sheets("Chart2").Name = "Chart1"
Sheets("Sheet3").Name = "Sheet2"
Sheets("Chart3").Name = "Chart2"
Sheets("Sheet4").Name = "Sheet3"
Sheets("Chart4").Name = "Chart3"
Sheets("Sheet5").Name = "Sheet4"
Sheets("Chart5").Name = "Chart4"
Sheets("Sheet6").Name = "Sheet5"
Sheets("Chart6").Name = "Chart5"
Sheets("Sheet7").Name = "Sheet6"
Sheets("Chart7").Name = "Chart6"
Sheets("Sheet8").Name = "Sheet7"
Sheets("Chart8").Name = "Chart7"
Sheets("Sheet9").Name = "Sheet8"
Sheets("Chart9").Name = "Chart8"
Sheets("Sheet10").Name = "Sheet9"
Sheets("Chart10").Name = "Chart9"
Sheets("Sheet9").Activate
'
' Add code to clear your data
End Sub

HTH,

Wouter.

 

[Gary''s Student]

1. Move your charts & graphs to a separate workbook
2. Close this separate workbook
3. Update your data worksheets, just be sure you end up with data worksheets
with the same names you started with.
4. Open the separate workbook (the one containing the charts, etc.)
5. Move the charts & graphs back to the original workbook


The trick is that while the charts are in the separate, closed, workbook,
they cannot "see" any changes to the data sheets.