Reset Sheet Count -Second Request Any Help Appreciated

Hopefully just a quick question, I have some code that gets run
everyday and it inserts sheets in a workbook and renames each sheet to
specific value. The next day the same code gets run and it deletes the
worksheets that i added the previous day and adds some new worksheets,
again changing the name of the sheets to a specific value and so on
and so forth each day of the week.

This works fine, however when the code inserts the new worksheet I
have noticed that the default name that excel gives the sheet(before i
rename it) is getting higher and higher. When i insert a new sheet now
the default name is up to sheet 541. 

So my question is, how do I reset it so that each time i run the code
it will the sheets will be inserted with the name Sheet1 or a
similiarly low number ?

Thanks for any help
0
Bill365 (4)
9/17/2009 3:49:08 PM
excel 39879 articles. 2 followers. Follow

3 Replies
381 Views

Similar Articles

[PageSpeed] 18

On Sep 18, 1:49=A0am, B...@aol.com wrote:
> Hopefully just a quick question, I have some code that gets run
> everyday and it inserts sheets in a workbook and renames each sheet to
> specific value. The next day the same code gets run and it deletes the
> worksheets that i added the previous day and adds some new worksheets,
> again changing the name of the sheets to a specific value and so on
> and so forth each day of the week.
>
> This works fine, however when the code inserts the new worksheet I
> have noticed that the default name that excel gives the sheet(before i
> rename it) is getting higher and higher. When i insert a new sheet now
> the default name is up to sheet 541.
>
> So my question is, how do I reset it so that each time i run the code
> it will the sheets will be inserted with the name Sheet1 or a
> similiarly low number ?
>
> Thanks for any help

I'm not sure how you are adding your sheets but this will create a new
sheet and name it for you.

ThisWorkbook.Worksheets.Add.Name =3D "I'm New"

hth

regards

David
0
dheaton (109)
9/18/2009 2:07:42 AM
Do you get the same result when you do the same operations manually? I tried 
to reproduce the behaviour you described but found that Excel (2003) always 
tries to name a new worksheet with the lowest default number. So if you have 
renamed Sheet1 to S1 and Sheet2 to S2, but keep an original Sheet3,  the 
next new sheet would be named Sheet1 by default. So maybe it has something 
to  do with your code. If it really bothers you then your code should create 
the new sheet already with the desired name (Sheets.Add.Name = 
"MySheetName") instead of creating a sheet and then renaming it.

Joerg

<Bill@aol.com> wrote in message 
news:8im4b5l4u52nha9vlj4r18h1vclc68uud2@4ax.com...
>
> Hopefully just a quick question, I have some code that gets run
> everyday and it inserts sheets in a workbook and renames each sheet to
> specific value. The next day the same code gets run and it deletes the
> worksheets that i added the previous day and adds some new worksheets,
> again changing the name of the sheets to a specific value and so on
> and so forth each day of the week.
>
> This works fine, however when the code inserts the new worksheet I
> have noticed that the default name that excel gives the sheet(before i
> rename it) is getting higher and higher. When i insert a new sheet now
> the default name is up to sheet 541.
>
> So my question is, how do I reset it so that each time i run the code
> it will the sheets will be inserted with the name Sheet1 or a
> similiarly low number ?
>
> Thanks for any help 


0
no3581 (99)
9/18/2009 2:16:46 AM
Hi Bill,

Save the workbook as a new name, close the file, rename the workbook back to 
its original name.

Peggy

<Bill@aol.com> wrote in message 
news:8im4b5l4u52nha9vlj4r18h1vclc68uud2@4ax.com...
>
> Hopefully just a quick question, I have some code that gets run
> everyday and it inserts sheets in a workbook and renames each sheet to
> specific value. The next day the same code gets run and it deletes the
> worksheets that i added the previous day and adds some new worksheets,
> again changing the name of the sheets to a specific value and so on
> and so forth each day of the week.
>
> This works fine, however when the code inserts the new worksheet I
> have noticed that the default name that excel gives the sheet(before i
> rename it) is getting higher and higher. When i insert a new sheet now
> the default name is up to sheet 541.
>
> So my question is, how do I reset it so that each time i run the code
> it will the sheets will be inserted with the name Sheet1 or a
> similiarly low number ?
>
> Thanks for any help 

0
9/18/2009 5:00:27 PM
Reply:

Similar Artilces:

Help with error 91 please
Hi, I've searched all over this forum and the help files to figure out how to fix the below macro which works up until it can no longer find the string and then I get a run-time error 91 on the cells.find line: Sub delregion() Dim x As String x = "region total" Do While x = "region total" Cells.Find(What:="region total").Activate ActiveCell.EntireRow.Select Selection.Delete Shift:=xlUp ActiveCell.EntireRow.Select Selection.Delete Shift:=xlUp Loop End Sub TIA for your he...

help 04-20-10
since yesterday when ever i try and open an icon on my desk top this message comes up windows cannot access the specified file, pathway you may not have permission to access it? what does this mean and how do i fix it???? Also about a week ago when i was on the internet these system messages kept coming up saying my computer was in trouble and peple were trying to access my personal information and i needed to upgrage my spyware. i couldnt go into any programes except the windows security site and then i had to buy the 6 month protection to access anything on my computer. now ...

Counting States basied on the time and date stamp
Afternoon All I am attempting to workout a quick and easy way of counting some results that are displayed in an excel spreedsheet. All of the results are date and time stamped, and also have a column that showes the state of the outcome [WA, NSW, QLD and so on]. What i am wanting to do is get a count basised on the state column for every hour? Can anyone assist? Cheers TJ Hi, TJ- I started a reply earlier this morning, and abandoned it hoping someone else would have a better idea. Since no one else has replied, let me take a crack at it. It can be done, altho it may seem a bit clum...

Currently no help is associated with this page.
I use Excel 2003 on Vista. A couple of days ago, I was getting this response to many helps such as F1; Search VLOOKUP; Click VLOOKUP. The problem went when I rebooted and tried again. I am hit again by this problem. I googled without finding anything. The problem seems to be with access to "local" help; Internet help is still found. Again, a reboot cured the immediate problem. I googled again and got 2 hits. Neither went anywhere effective. I would appreciate an explanation and a less destructive fix. Thanks! -- Walter Briscoe Some versions of Excel allow you to enabl...

Help needed with Multipage UserForm (VBA)
Bear with me folks, I'm a newbie when it comes to VBA: In John Walkenbach's book "Excel 2002 Power Programming with VBA," there is an example of a multipage UserForm (Wizard) where the data is inserted in contiguous cells of a (single) active sheet. I'm looking for the syntax (or better still, an example) for inserting the data inputs in specific cells in different worksheets, in a multi-worksheet workbook. Any help will be appreciated. Not quite sure what you're looking for, but maybe: Option Explicit Private Sub CommandButton1_Click() With ActiveWorkboo...

Simple Date problem -_- Please Help !
Hi everyone, I have list of times in hh:mm format from cell A1 to A730 e.g. 9:30, 13:00, 13:32, 12:12 etc... How do I put number of minutes passed from 9:00AM i.e. if A1 has 9:30 then B1 must display 30, if A2 has 14:12 then B2 displays 312 and so on. What kind of function do I need to use? Please help! Thanks alot :D format cell as [mm] and use =A1-TIME(9,0,0) James8309 wrote: > Hi everyone, > > I have list of times in hh:mm format from cell A1 to A730 > > e.g. 9:30, 13:00, 13:32, 12:12 etc... > > How do I put number of minutes passed from 9:00AM > >...

Enhancement Request
To whom it may concern: I would like to request an Access feature. I would like to request a window that shows the status of an Access query in process. i.e. data load in progress, calculation in progress, processing record x of y, etc. I regularly run access queries on tables with millions of records and I would like to be able to see the status of a query to get a sense of whether it is still running and how long I might have to wait. Thank you and best regards, -- Gus Gilbertson Finance Seattle, WA ---------------- This post is a suggestion for Microsoft, and Microsoft responds ...

Meeting Request message not recognized in Outlook
From an Outlook 2007 PC we send a meeting request to several users. One user running Outlook 2003 has trouble with the meeting request. The user receives the email but he can only see the header and it doesn't allow the user to "accept" the meeting request. TIF "A~T" <AT@discussions.microsoft.com> wrote in message news:6D39F9C8-C046-43BF-B937-2D270190115B@microsoft.com... > From an Outlook 2007 PC we send a meeting request to several users. One > user > running Outlook 2003 has trouble with the meeting request. The user >...

urgent help
hi members how can I insert rows after every fiftth row I have data of more than 6000 rows that means I want to insert 2000 rows in that sequenc -- b166e ----------------------------------------------------------------------- b166er's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3491 View this thread: http://www.excelforum.com/showthread.php?threadid=55456 See other post. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "b166er" <b166er.29t4vb_1150986001.8819@excelforum-nospam.com> wrote in message news...

help hyperlinks
plz help me I have a spreadsheet column containing hyperlinks (paths) t different jpg files. How can I get it to work so that if I click on an of the hyperlinks, it will open the jpg in a Imaging application? using micros thank -- Message posted from http://www.ExcelForum.com Hi If you have created hyperlinks using Excels 'Insert hyperlink' functionality and an image application is associated to *jpg files clicking on such a hyperlink will automatically invoke your application But if you currently only have the filename as text (that is no hyperlink), you may try the following in...

Help Creating XmlNode / XmlAttribute Elegantly
I am creating a configuration class to read / write a standard configuration file. <?xml version="1.0" encoding="utf-8"?> <configuration> <appSettings> <add key="ConnectionString" value="server=(local);" /> </appSettings> </configuration> I am using a XmlDocument for my base implementation. I am looking to see if there is a better way to add a new node / attribute than using XmlDocument.InnerXml. I would consider this to be a hack but couldn't really see another way of doing this without definin...

CRM Security Services (Request timed out)
I installed CRM and it works well but i have one problem that when i come to office every day and chech the CRM server it stoped and when try to start it http://crmweb then i get message Security Services is not runing and after runing Security Services when i try to run Explorer http://crmweb then it hangup for long time and mostly i get error after shut down and restart server it works well. i dont know what is wrong with this? During hangup i get this error message Server Error in '/' Application --------------------------------------------------------------------------------...

Database query with parameters given in the sheet
Hi all, I've created a new database query with inner joins and sorting, which gives great results. But, there's only one thing I can't figure out on how to get this thing working... I'm using Office XP, and I try to make a sheet in which a user can specify a "from" date and a "to" date, after which the query starts only in this range. However, when I try to create the criteria, there's no way I can use variables or named cells in any way. Can anybody tell me a workaround for this matter? Maybe this is something for Microsoft to pun in new versions in...

Item / ItemDynamic Help
We are running HQ and SO 1.3r. We have an "outside" application that has loaded some items into the HQ database. However, the programmer failed to load the corresponding ItemDynamic entries. Can someone provide me with a SQL program that will search through the ITEM table and add the appropriate ITEMDYNAMIC entries for each of our stores? So, far - no inventory has been received for these items but they soon will be and then this problem will only be compounded. Your SQL expert assistance is requested. Thank you ... in advance. This is a multi-part message in MIME format. -...

Need help in coding outlook
Hi, For Each oMailItem In oInbox.Items Debug.Print oMailItem.SenderName Next The above codes are part of my code. The issue is when the inbox item is not mail item the code will be error. When the inbox item is a report item (delivery failure notification) the code will be error. How to fixed that? Regards, Gunawan You can use the .Class property to determine what kind of item it is, before you invoke the properties and methods of a particular type of Outlook item. FYI, there is a newsgroup specifically for general Outlook programming issues "down the hall" at microsoft.pu...

help with uniforms
I have a database, I use for ordering uniform and keeping a check on employees allocation. The company we use to supply the uniform have changed their pricing structure, which is causing me a few problems. Any outsize orders are liable for a 10%, 20% , 30% or 40% mark-up At the moment all my prices are generated when you pick an item from a combo box, it automatically fills in the price. The size is input by the user. Because of the pricing changes and the sizing differences with my stock: Shirts 14” –22” Blazers 34” - 58” I was hoping to have a combo that recognises the 14” – 18.5...

Help with vba autorunning macro?
Hello, How do I automate (write code in Access) to tell it to run a macro I had created in Access everyday at 6AM? I tried using Windows XP Task Scheduler, but it didn't work. Any help is appreciated. THanks What does "it didn't work" mean in the context of trying to use Task Scheduler? What command line did you try? -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) "Cam" <Cam@discussions.microsoft.com> wrote in message news:99EABB88-3DDA-...

Confident no one can help with this booklet issue...
Please tell me what the problem is with printing booklets. Everytime I print one the First days of teh calendar appear on page one and the the last three days and the notes appear on page two. In other versions of Outlook they printed on page two and three. Which is the way they should. Is no one printing out calendars any more? I've posted many times for help on this and can't get any. I'm using outlook XP. Is there a fix for this problem? Eric ...

Help settings up SBS 2003 with PPPoE
Hey everyone, We currently have a server in our office running sbs 2003 with a single NIC with client computers connected. We were just using a dynamic IP through DSL, but switched out to a static. I am working on setting up the static through PPPoE but know adjustments must be made. Can anyone guide me through the setup? I had trouble finding a guide online. Do I need to switch to a two NIC config? What adjustments need to be made through the router and sbs connection set up? I'm a little confused on all of it. Any help would be appreciated!! Thanks Alex What r...

HELP: Need to transfer/share files between 2 PC through Internet.
I need to develop a program to conncet two systems through dial up connection and to share the files/folders of that tho systems.... any idea.. any guidance... warm welcome..... regards, Aji. ...

Need help to find right solution
Hi I cant find the right way to complete my task. I have customer table in acces, about 800 records (can be exported to txt, xml excell, whatever). Then I need to create a form, to let user easily choose the customer and add some data to another field. And there is the problem. I cant find a way to easily choose the customer. List field doesnt work well as it takes time to find customer, any searching coul be done but it needs separate worksheet. I was wandering if I could do for example searching customer in another worksheet with button near every record "Add" so user can add pos...

Need a UDF to count blank cells
HI , and a good day to all programmers I need a UDF , which to count non blank cells above the cell which contain this UDF . When the UDF find the first non blank cell , to stop the count . Example : A1 - nonblank A2 - blank A3 - blank A4 - blank A5 - UDF here , the result of UDF from cell A5 must be 3 . I need to count only above in the column , not in the left or right . Thank you > I need a UDF , which to count non blank cells * sorry , to count blank cells . Hi, Blank is an often confusing term with regard to Excel and here I've taken you literal...

Multiple Users with Outlook and syncing with an IPAQ
I have XP2 Home running Outlook Express v6 but I want to use Outlook instead (I have Outlook 2000). However my wife and I both use Express and we use the Switch Identity feature. Is there a similar feature in Outlook proper? Also, we regularly have to reboot to switch identy anyway. Finally, I'm syncing via Activesync an IPAQ 3870 running an old version of pocket PC and it doesn't seem to want to establsih a partnership. I'm struggling to get a support resource - any ideas pls? many thx RobertP <robertpepper@hotmail.com> wrote: > I have XP2 Home running Outlook Expr...

Error 3045 on an MDE file
Hi I hope you can help me with this it is driving me nuts. I've created a database which I needed to give multiple people access to but didn't want them messing so I created an MDE file. I ensured that the MDB file was set to shared, not compacting and set to run users not owners permissions before I converted. However, if one of the users goes into the database, it does not create and LDB file and if anyone else tries to get it, the error message: Could not use <name>; file already in use. (Error 3045) appears. Some of the tables in the MDE files were linking to and MDB fi...

Counting values in an array where value in one column is not null
Hi, I am collating project details from multiple sources and need to report back how many projects are active in specific phases based on data such as in the table below: Projects Phase Version "Start Date W/c" "End Date W/c" A Prep Build Deliver a1 5-Apr 26-Apr Close a1 26-Apr 26-Jul B Prep Build Deliver b1 5-Apr 26-Apr Close b1 26-Apr 26-Jul C Prep Build c1 19-Apr 3-May Deliver c1 5-Apr 19-Apr Close c2 3-May 2-Aug D Prep d1 5-Apr 5-Jul Build d1 26-Jul 2-Aug Deliver d1 5-Jul 26-Jul Close d1 2-Aug 1-Nov If the start ...