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!
0
Utf
4/19/2010 5:31:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
521 Views

Similar Articles

[PageSpeed] 53

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.
0
Wouter
4/19/2010 6:09:07 PM
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!
0
Utf
4/19/2010 6:17:01 PM
Reply:

Similar Artilces:

change fonts for specific fields in a report
A Great Plains report prints checks. The check amount field is too small and needs to be increased. How does one change the font of a particular field in a Great Plains report? If the report is a text report, you can't change the font. If it's a graphical report, click on the field and press <Ctrl-D>. "caincognito@yahoo.com" wrote: > A Great Plains report prints checks. The check amount field is too > small and needs to be increased. How does one change the font of a > particular field in a Great Plains report? > > If it is a text report, you...

link to specific sheet in excel file which contains points and spaces
I am trying to make a link in Acces to an Excel file. This excel file contains several sheet with different names. My form in Access has a field that corresponds to the name of the excel sheets (Formname in script). When the name in the form contains points or spaces my link to the sheet with the same name does not work. I tried to put brackets [] on either side of the name, but then linking does not work at all. Please let me know if you have any suggestions. Code (works when Formname.Value does not contain spaces or points etc): Private Sub CommandActionRegister3_Click() Dim Locatio...

Where to change my own internet adress in "FROM:" field?
I am using Outlook 2003 with Exchange server. My own eMail address is presented to recipients as Aaaaa.Bbbbbb@ccccccc.com Note: mixing of capital and lower letters I want my email address completely changed to lower letters: aaaaa.bbbbbb@ccccccc.com Where in Outlook can I change this? or is this a setting in Exchange Server ? Joe This is a setting in ADUC/Exchange. I would guess that everyone is set up with the same format throughout the company. -- Kathleen Orland - MVP Outlook Outlook Tips: http://www.outlook-tips.net/ http://www.howto-outlook.com/ "Joe deAngelo" wr...

Changing menu bar height
Hello, is there a way to change the menu bar height of an application? Regards, Nicola I dont see how that can be done without replacing the whole menu mechanism. You can do that if you want by putting in a toolbar/rebar which acts as a menu. Example MFCIE shows how to make a toolbar as a menu. YOu can change the height of the toolbar/rebar to what you want. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Nicolas Bonamy" <mynab@hotmail.com> wrote in message news:OUgmBPOjDHA.2644@TK2MSFTNGP10.phx.gbl... | Hello, | | is there a way to change the menu bar height of an appl...

Forward misspelled names to user account if doman is correct.
Is it possible to catch for instance a misspelled name like moike@domain.com that should be mike@domain.com and forward it to another account? It should be able to pick up anything (like *@domain.com) and forward it to joe@domain.com. Thanks in advance. Mike Are you really sure that you want to do this? If so, you can take a look at the methods listed here. http://hellomate.typepad.com/exchange/2003/08/exchange_catcha.html -- Ben Winzenz Exchange MVP MessageOne "pakitloss" <pakitloss@discussions.microsoft.com> wrote in message news:24DB863D-49F5-457A-BA47-B7D26F422...

Named as rlbook1, re-open? How?
....during library internet sesion this occurred. I don't understand your question. Please put the entire question in the body of the message. It's rather cryptic now. "rlscreen001" <rlscreen001@discussions.microsoft.com> wrote in message news:577EA294-53D3-4D94-A042-7CB9C58B7F64@microsoft.com... > ...during library internet sesion this occurred. ...

User folder on desktop has a bad name
My name is Matteo (like User Name). Hence I had a User Folder on desktop named Matteo. Now it has a different name, but files inside are C:\Users\Matteo The wrong name is visible only on desktop How can I restore the correct name? "Allamarein" <matteo.diplomacy@gmail.com> wrote in message news:c9cd2878-a7ad-46c5-b802-a37c9d4f5b7a@d39g2000yqa.googlegroups.com... > My name is Matteo (like User Name). Hence I had a User Folder on > desktop named Matteo. Now it has a different name, but files inside > are C:\Users\Matteo The wrong name is visible only on deskto...

Changing positive values to negative
I’m using excel 2007with windows xp pro. I use excel very infreuently and not since upgrading to 2007. I have a long column of dollar values that I need to change from positive to negative values such as: $123.45 ($123.45) I can accomplish the feat manually over many hours by F2>.HOME>- > ENTER> DOWN ARROW and repeat and repeat and repeat. Or I can use a macro. But using a macro has changed from years ago. When I tried the macro inserted a specific value of cell rather than the value of the selected cell. So that didn’t work. What can make this conversion easier? If t...

Exchane 5.5 IMC Naming
I need to change the FQDN display name on a 5.5 Exchange IMC. Currently the server displays netbiosname.localdomain, where it should read the FQDN of the server. I've searched the registry and misc. config files in the Exchange directory to no avail. In Exchange 2K + you can set this in the server SMTP config. However there is no place to set this in 5.5. Any ideas? Thanks for the help. We ran into a similar issue. We found our options to be either configure our reverse lookup records to our internal namespace or change the domain suffix of the smtp machine. We ended up changing ...

need registration name and key for reinstall
I purchased Money 2007 Premium in August, 2006, via Digital River download and I also ordered a backup CD. Everything worked fine until my computer died. Now I am trying to install Money on a new computer using the same backup CD. But it pops up a window entitled "Key Expired". It appears that I need a "registration name and key", "exactly as given to you". Perhaps I did receive this information before but it would now be lost on my dead computer. Is there a way to retrieve the requisite magic cookies from someone's web site? And what credentials...

Pink Sheet stocks
I am using MS Money 2005 Premium, Version 14.0.125.1105 and have recently had a need for Portfolio Manager to list and update the price on a stock that slipped into the Pink Sheets. I can not get Money to recognize this stock symbol, although it is valid in Yahoo. What do I need to do, or does Money not stoop to that level? This stock ABRXQ.PK is one I made the bad judjement of keeping when it fell from grace. It opened this morning in the Pink Sheets. This was ABRX a few days ago, then it was delisted from the Nas and became ABRXQ. This morning it was handed off to the pink sheets and acquir...

Formula auditing on protected sheets
Simple question: is there a easy (or difficult?) way to track precedent and dependent cells on sheets which are protected? Thanks, Andrew ...

Check Name returns No Suggestions
To the group: Outlook 2003, Windows XP I type in a name in the To: box and click the Check Names icon and it returns 'No Suggestions' The Order of Check Names Address Lists is set to Contacts first (Address Book > Tools > Options) - kb296948 I have reset the nickname and automatic completion cache - kb287623 I have even deleted some of the auto-complete names - kb292928 Still I get 'No Suggestions' when I type in a name I have in my Contacts. Any assistance or suggestions most welcome. Thank you. John Can you give an example of what you're typing in and wh...

Get/change operation on Table
I am trying to set up the transit numbers in the bank Maintenance screen (View>>Financial Lists>>Checkbooks ->under the Bank ID link). When I try to save the changes made to the Bank Maintenance screen I get the error a get/change operation on table 'ME_Pay_Check_Setup' cannot find the table. If I press ok, I get another error stating that I "Cannot run the trigger script." If I Press More Info I get a SQL error stating that it is an invalid object name. Any suggestions on how to fix this would be greatly appreciated. You have the Mekorma MICR Check...

Adding data from another sheet.
I hope I can find some help on this one. I have a spreadsheet that consists of two sheets. The first is a form. The second is a list of questions. I have added a checkbox next to each question on sheet 2 to select the question and add it to the form on sheet 1. The problem: when you select a question on sheet 2 it will only add it to a specific cell on sheet 1. So if I select question 15 it will go in a position like it is the 15th question when actually I would like it to be the first question. The question: Is there a way to make the questions start adding to the top cell on sheet 1...

Password Change
Hi, I am using Money 2007 Deluxe and want change my password at a financial institution. The change at the bank works fine, but Money keeps reverting to old password. Hee's is how I try to make change. From the account register, I go to "Change account settings" then "Change bank or brokerage passwords" then "Edit user ID or password" I then type, not copy and paste, my new password and "Save Changes" I then try to update online and am told password and or user id, incorrect. When I go back to "Edit user ID or password" my old password i...

How to change owner (From:) of a public folder, newsgroup post
How do I change the Owner or From: for a newsgroup post. This is a post in an Exchange 2003 public folder of type newsgroup. The posts in question are from a generic user account and I need to change it to the specific user. I tried by opening the post, choosing Edit menu -> Revise Contents, and changing the From: to the new person. But it would not save, and if I reposted it would keep the original user (poster) in the From: field (and of course it also changed the post to the current date/time). I have tried this method, to no avail, using the original user, and two different a...

No name with Contacts
I'm using Outlook 2002. When I save a new contact, it appears blank in my contacts. The phone number will show but that is the only way I can identify the contact. Sometimes I get a message that the "File As" field is blank, although it isn't. Not sure what made this start happening. Those that appear in my contact list with names are fine. I can make modifications and the name still appears in the list of contacts. Only new contacts are saved with blank names, even though their File As field has a name in it. I've tried repairs, uninstall/reinstall, MS KB but not...

named range scope...
i get confused... a named range can have: a global level (to the entire workbook), or a worksheet level (to the active worksheet), or a ... also, i've really looked, but can't find good information on how to define 1 level name vs. the other... (any direction on this ?) Mark, Just the two. A workbook name is created by just inserting a name in the Names box, such as myRange. A worksheet name is created by adding the sheet name to the name in the Names box, such as Sheet1!myRange. You can then also create Sheet2!myRange. You can only add a worksheet name if ...

Excel 2007
Hi all, We have a spreadsheet that was set up to link to a database via a system DSN. To simplify deployment of this, I'd like to change this to connect via the connection string below: DRIVER=SQL Server;SERVER=xxx;UID=xxx;PWD=xxx;DATABASE=xxx; I've done this for another spreadsheet, and it was easy (Data tab- >Connections->Properties), but in this next spreadsheet the Connection Properties for one connection are greyed out/disabled, so I can't change the string. Does anyone have any ideas how to enable these values again so I can change the connection string? thanks! ...

Passing control name to sub
I'm having trouble passing a MSFlexGrid control to a sub. The control name is "flxGalleries". The error is "Type Mismatch. Error '13'". I have the following code: Private Sub Form_Open (Cancel as Integer) ... do stuff ... GridSetup flxGalleries 'Call the Sub and pass the Control ... do more stuff ... End Sub Public Sub GridSetup (ByRef flxGrid As MSFlexGrid) With flxGrid ... do stuff ... End With End Sub What have I done wrong? Cheers Try: Call GridSetup(flxGalleries) 'Call the Sub and ...

Change reference in formula from cell address to name
I'm fairly sure I saw this yesterday somewhere in this forum, but after almost two hours of searching, and not finding, I figure I've earned the right to ask - if only because someone should feel sorry for my pathetic search skills (or bad memory). Example: say I have a formula =$A$1+$B$2. Later, I name cell A1 "start" and B2 "finish". The original formula above still has the cell references and not the cell names (it does not change to =start+finish). How can I get the formulae created prior to naming cells to change and reflect the cell names? Secondly, give...

change cell values from an edit box
Is there any way to change a cell's value with an edit box? I've used drop down lists to change/update values, but I have no experience at all with edit boxes. Any help would be greatly appreciated. Mike ...

File Name Length
Does anyone know if filenames can be longer than 31 characters in Office 2004? Thanks. In article <19bee01c41cec$47393e90$a401280a@phx.gbl>, "JM" <doubledawg9498@yahoo.com> wrote: > Does anyone know if filenames can be longer than 31 characters in Office > 2004? Thanks. FIrst, you *can* have filenames longer than 31 characters in Office v.X already - you just can't generate them. While v.X apps display a hash at the end of a long filename, it keeps the filename intact when you save. MS hasn't announced anything regarding long file names in 2004 ye...

GAL display name
How do I change a user's display name in the global address list? I already changed it in active directtory users and computers. It still shows up as : bill smith instead of wsmith. I need it to be wsmith You can right click on it in ADSU and choose rename, it will then bring up a dialog box, and you can change the display name there. -Drew "Eddie" <Eddie@discussions.microsoft.com> wrote in message news:B334F293-62BB-444B-B6E0-B6385A685BBF@microsoft.com... > How do I change a user's display name in the global address list? I > already > changed it in ac...