Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet name changing dilemma
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet name changing dilemma
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet name changing dilemma
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. -- Gary''s Student - gsnu201001 "Danielle" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a sorting dilemma | Excel Discussion (Misc queries) | |||
What if Dilemma | Excel Discussion (Misc queries) | |||
BIG Dilemma.....HELP!! | Excel Worksheet Functions | |||
If Then Dilemma | Excel Worksheet Functions | |||
XL add-in dilemma | Excel Programming |