Check if a sheet exists

I have a workbook with a number of sheets (n) that changes.

Each sheet in the workbook is named; "      Sheetn   " where n is th
sheet number (e.g. Sheet1, Sheet2, Sheet3 etc.)

I need to know the VBA to check whether a sheet exists; something alon
the line of this

sheetloop = 1

do

domacroonsheet(sheetloop)   <--- My other macro that does stuff

sheetloop = sheetloop + 1
loop until exist(sheets("Sheet" & sheetloop))=false


Can someone correct this for me please; I'm desperate to get thi
working!

Many thanks in advance! :confused

--
Message posted from http://www.ExcelForum.com

0
5/5/2004 1:07:11 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
391 Views

Similar Articles

[PageSpeed] 21

Hi
one way to check for an existing sheet:
dim wks as worksheet
on error resume next
set wks = worksheets("sheetname")
on error goto 0
if wks is nothing then
msgbox "sheet does not exist"
end if

>-----Original Message-----
>I have a workbook with a number of sheets (n) that 
changes.
>
>Each sheet in the workbook is named; "      Sheetn   " 
where n is the
>sheet number (e.g. Sheet1, Sheet2, Sheet3 etc.)
>
>I need to know the VBA to check whether a sheet exists; 
something along
>the line of this
>
>sheetloop = 1
>
>do
>
>domacroonsheet(sheetloop)   <--- My other macro that does 
stuff
>
>sheetloop = sheetloop + 1
>loop until exist(sheets("Sheet" & sheetloop))=false
>
>
>Can someone correct this for me please; I'm desperate to 
get this
>working!
>
>Many thanks in advance! :confused:
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
frank.kabel (11126)
5/5/2004 1:18:09 PM
Ok; I've coded this in. The problem is the loop drops out at 32000 is
without trapping the "Nothing" condition. Where have I mucked up?!

Dim numberoftitles As Integer
Dim looper As Integer
Dim wks As Worksheet

'Calculate Number Of Sheets
loopsheet = 0
Do
loopsheet = loopsheet + 1
On Error Resume Next
Set wks = Worksheets("Sheet" & loopsheet)
On Error GoTo 0
Loop Until wks Is Nothing
numberoftitles = loopsheet - 1


So close...! ;

--
Message posted from http://www.ExcelForum.com

0
5/5/2004 3:20:52 PM
Hi
not tested but try
Dim numberoftitles As Integer
Dim looper As Integer
Dim wks As Worksheet

'Calculate Number Of Sheets
loopsheet = 0
Do
set wks = nothing
loopsheet = loopsheet + 1
On Error Resume Next
Set wks = Worksheets("Sheet" & loopsheet)
On Error GoTo 0
Loop Until wks Is Nothing
numberoftitles = loopsheet - 1

--
Regards
Frank Kabel
Frankfurt, Germany


> Ok; I've coded this in. The problem is the loop drops out at 32000
ish
> without trapping the "Nothing" condition. Where have I mucked up?!
>
> Dim numberoftitles As Integer
> Dim looper As Integer
> Dim wks As Worksheet
>
> 'Calculate Number Of Sheets
> loopsheet = 0
> Do
> loopsheet = loopsheet + 1
> On Error Resume Next
> Set wks = Worksheets("Sheet" & loopsheet)
> On Error GoTo 0
> Loop Until wks Is Nothing
> numberoftitles = loopsheet - 1
>
>
> So close...! ;)
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
5/5/2004 3:38:03 PM
Works a treat! Thanks Frank

--
Message posted from http://www.ExcelForum.com

0
5/6/2004 7:44:02 AM
Reply:

Similar Artilces:

merge MSN Bill Pay & Checking Acct
I want to merge my checking account with MSN Bill Pay but only see that I can merge MSN Bill Pay with my checking account (i.e., you lose all of your checking account information). Does any one have any suggestions? -- cheers, Tom G ...

Linking Cells Between Sheets
This very straight forward function no longer works for me. When I enter "=" the cell reference cell changes to "ACOSH" as opposed to "VLOOKUP" on other computers. This will not eference cells on other worksheets within the same spreadsheet and even worse, changes seamingly random cell values in existing spreadsheets, often creating circular references. Nobody seems able to help and a the moment I am considering re-installing office! ACOSH is one of the Trigonometric Functions Simply typing an "=" sign in a cell should not invoke VLOOKUP or any ...

Check names not working for multiple address books / contacts
Hello! I have upgraded from outlook 2003 to outlook 2007, my contacts are stored in pst. I used to be able to search for a name via check names and it displayed me all the results from global address list and from my contacts, but now it only does one. It does not matter which one I set in the checking order, whichever is top will be the one that provides the result. I would like it to show me both results, how would I go about to set that up? Thank you Deana More accurate and complete information would be helpful. The Global Address List is only available to those who c...

Authorizing checks with RMS
I recently removed PCCharge and setup my syetm to work with a preferred merchant provider to allow native processing of credit & debit cards. Since check verification/authorization was handled by PCCharge to Telecheck, check authorization is now not being done. How is check authorization accomplished using a preferred provider? Thanks, Richard Unfortunately, it's not. PC Charge is the only option for check processing in RMS as far as I know. TPI or some other vendor may support it through an add-in, but RMS as shipped has no way of doing both checks and debit... -- -- Gle...

How do I create Check list
1.I am new to excel and want to create a simple check list. I would like to have the first column check boxes and then the following columns discription and notes. Can someone give me a step by step on how to do this. I have created spread sheets but am not familiar with check boxes. 2.Also, Can someone tell me the difference between Access and Excel? Geek 1) Not sure what you're aiming to do with the checkboxes, but if you right click on the toolbar area, you will get a list of available toolbars. You want either the 'forms' toolbox or the 'controls' toolbox. Genera...

Preventing multiple check boxes from being checked
I have a worksheet with over 20 rows of check boxes with 5 check boxes in each row. (from the forms toolbox) I would like to prevent the user from checking more than one box in each row. Is this possible? BTW, this forum has been a great help. I'm so glad that expert Excel users are so willing to share their wealth of knowledge. Thanks again. Jae Put them in a group box, again from the forms toolbar. Drag the group box onto the sheet, then drag the checkboxes in. -- HTH RP (remove nothere from the email address if mailing direct) "Jae" <Jae@discussions.micro...

Check text field value against underlying table or query of combo box
Hello, I have a form on which the user can enter new records or update existing ones. If the user enters a new record on the form, I would like to have an 'After Update' event on one of the text fields, which is the item# to check if the value entered already exists in the underlying table, tbl_item. How can I call the table from the 'After Update' event of the text field and check for that value in the table? Thanks in advance. Regards, A. Crawford antcraw@swbell.net wrote: >I have a form on which the user can enter new records or update >existing ones. >If the us...

cheat sheet
I'm looking for an Excel cheak sheet or quick hints list. You mean shortcut keys? Debra Dalgleish's site: http://contextures.com/excel.htm Chip Pearson's site: http://cpearson.com/excel/ShortCuts.htm Marilyn wrote: > > I'm looking for an Excel cheak sheet or quick hints list. -- Dave Peterson ...

Printing Checks
I'm having a problem printing checks from Money 2003. I have three accounts that come up in one .mny file. Two of the accounts use 3-on-a-page checks and I want the name and address printed on them. One account uses voucher checks and needs to be nudged a little south. When I go to print checks I have to be aware of what account I'm in and change the settings by hand to make sure the checks print correctly. How can I make the check-print settings stick with the account I'm using. It's a pain to have to remember every time. I'm frequently forgetting and screwing up a pag...

Tracking Sheet
I have a tracking sheet that is set up in excel07 and used to track an entire project for capital equipment builds. The spreadsheet has columns A thru BA and the number of rows are dependent on the number of parts within that project. However, the headings are universal. My issue is that there are crossfunctional groups using, and populating information into this sheet at different times. Is there a way that I could implement a macro for views or some way that I can identify columns viewed at the click of a button, so if I click the "BUYER" button, I only see colums A(i...

Employee Check Format-Graphical
Does anyone know of a TK that shows how to convert one of the employee check from a text report to a graphical report? We do not have Direct deposit, I looked in the Report Downloads from Great Plains and did not see one there either. thanks -- Doug There's no secret here. Just un-check the "Text Report" option in the Report Definition window. And then modify the report so it looks like you want it. This will take some time. The reports don't transfer from text to graphical nicely. "Doug" wrote: > Does anyone know of a TK that shows how to convert...

local named references in a sheet
Hello! (this may be a repitition of a message I think I succeeded in seding previously, but cant retrieve, so Im trying again. Appologize!) I am trying to copy a sheet with many named references into a new sheet (same workbook), but I want the new sheet to have the references local to it, so I can experiment with different data on the same calculations. The references hold intermediary values. When I try to copy the original sheet, the named references in the copy point to the original sheet. How do I copy it "correctly"? Thanks Hi Michael, > When I try to copy the orig...

Check Names = "Exchange Server computer is busy..."
Here's what I got: Active Directory on Server 2003 1 Domain Controller also running DNS and WINS No other DNS or WINS servers (I know, I know) Static IP Addresses...NO DHCP Exchange Server 2003 on a member server Client running W2K SP4 Outlook 2000 SP3 Exchange has 4 mailboxes set, but nobody is using it. I'm still testing things out. When I try to connect to Exchange via my Outlook 2000, I get the following message: "The function cannot be performed because the Microsoft Exchange Server computer is busy. Try again later" I've tried it from 3 different client comp...

Printing MICR checks
Hello, We are having an issue printing checks on the Select Payables Checks window. When we try to print the checks we receive the error message below: "Warning: You have MICR but are not in a MICR screen. To use MICR, abort this screen and set up security access." The user has access to this screen but I'm not sure what this error message means. There was nothing related to this in the Knowledge Base either. Has anyone else received this error message before and if so how did/do you resolve it? Thanks! The user has the third party mekorma check writing module ...

Loan Acceleration Sheet
Here is my desire...any assistance in finding a preadsheet to do this would be greatly appreciated. My offer in return are my ideas listed below. Many mortgage companies offer "equity accelerator" program in which automatic deductions are made from your accounts TWICE a month (such as the 1st and 15th) rather than just one payment. The two amounts generally equal what would have been the monthly payment. The idea is that my making more payments, that even though the amount per month is the same, the interest that accumulates is less and thus your home equity is "accelerated&...

Check off boxes
Publisher 2003 in use. I am creating a questionnaire that will be included in a newsletter. I am using the publisher software to make this form. Somewhere, in the past, I saw a keyboard shortcut to inset checkoff boxes, however, I cannot remember the key strokes. Any help along this line will be greatly appreciated. Thank you and enjoy your day. Mike Mike Reshetar wrote: > Somewhere, in the past, I saw a keyboard shortcut to inset checkoff boxes, > however, I cannot remember the key strokes. Use Wingdings and the � or q characters. -- Ed Bennett - MVP Microsoft Publisher h...

finding and then moving entire row to new sheet based on cell value
I have a sheet with several thousand rows that looks something like this: name address city search-term I want to be able to "search" the search-term column for a key word and then move entire row to a new sheet There are several examples here I have tried none seem to do above! Thanks for any guidance. Dave EggHeadCafe - Software Developer Portal of Choice Using COM-Callable Wrappers to Extend Visual Basic 6.0 http://www.eggheadcafe.com/tutorials/aspnet/bba810dd-6e59-4603-8bd9-accc915af876/using-comcallable-wrappe.aspx > I have a sheet with several thousa...

check check check
afpoiadshfpaosid It is a feature, not a bug -- Message posted from http://www.ExcelForum.com ...

spam properties check
When I receive a suspecious email (spam), I right click, without opening the message, to check the properties to make sure it is spam before I forward to the "missed spam" software of my ISP. Some spam emails then ask me if "I want to save the changes" before I close the properties box. I am then not able to forward this email without replying yes to "save the changes?" I did not make any changes, I just checked the properties. Why is this happening? ...

Removing SRS when exchange 5.5 server no longer exists
I have inherited a nightmare of an exchange installation. Initially, the exchange 5.5 environment was upgraded to Exchange 2003. However, before the Exchange 5.5 server was successfully removed from Exchange, it was turned off and taken out of the network. As a result, previous administrators have attempted many different things to remove all references to this server from Exchange/AD. However, this first Exchange 2003 server is itself now to be decommissioned, but I can't uninstall exchange because it detects Site Replication services as being on this server "You can not unins...

tristate check box #2
I have a tristate check box.The default behavour of tristate check box in windows is that, the grayed check box will become unchecked after click on grayed check box. But i want , the grayed check box will become checked after click on grayed check box in VC++ .Pls help me its urgent. Well, for Win32, you can always SendMessage the button a BM_GETSTATE. I'm sure with MFC there's a function that will permit this; it could be as simple as button_name.GetState(). Well, for the event OnClick for that button, you can get the state of the button and then either run it through some if-...

Check scanning and Check readers
Has any group successfully integrated a check reader or scanner into a MSFT RMS-POS solution? Our small business processes alot of checks from contracts and cash from customers. So, we need to integrate credit card, debit card, cash, and checks processing. I've been wanting to implement RMS but have been unsuccessful in locating info on any check readers and compatibility with RMS. Anyone with a solution to reading, scanning, and electronically verifying checks at the POS? Rms only supports check verification using PC Charge. Also, RMS only supports MICR (Magnetic Ink Character...

Max Units
Does the max units have to be a percent? I am using Project 2003. Thanks! Yes. Why, what you prefer it to be? -- Trevor Rabey 0407213955 61 8 92727485 PERFECT PROJECT PLANNING www.perfectproject.com.au "Cori" <Cori@discussions.microsoft.com> wrote in message news:688E5D24-F1CE-4D35-9567-64E169DDD78E@microsoft.com... > Does the max units have to be a percent? I am using Project 2003. > > Thanks! This is a multi-part message in MIME format. ------=_NextPart_000_0039_01CAAA31.A4534340 Content-Type: text/plain; charset="Windows-1252...

Lotus co-existence
Hi, I have my scenario as follows : Internet >> Smart Host > Back-end Exchange & Lotus environment I want to maintain the external domain namespace for incoming emails. In that case, my assumption of the server placement and flow would be as follows: Please someone validate and correct if i am wrong: Internet >> SmartHost >> Lotus Connector >> Exchange and Lotus env My query: Is above setup possible? Does Lotus Connector be able to differentiate and route the emails? Any help appreciated. thanks, in other words or to cut-short, is it possible to confi...

Check Void in Error
Our beloved cash admin voided SEVERAL checks in error. Is there a way to un-void them? They were historical checks, ie. not in the current period. Our bank isn't effected, but our ledger is. I'm pretty adept at Sql, what tables would I need to hit if I have to manually do this?? Thanks! ...