How to verify specific Worksheets are present
Windows 2k Pro
Excel 2000 I have a Workbook on which I need to do a relatively simple check using VBA code. I want to scan the Workbook for four specific Worksheets (for example the worksheets are named "Red", "Blue", "Green" and "Purple"). If any of these are missing then I'd like to create a new Worksheet with the missing name(s), AND I'd like to delete any Worksheets that are NOT named with one of the four mentioned names. I'm pretty sure I could do this, but I'm afraid my method would not be very efficient at all. How many steps could this be done in? Or, what is the most efficient way to do this? TIA! -gk- |
How to verify specific Worksheets are present
Sub addsheets()
Dim item As Variant For Each item In _ Array("red", "green", "blue", "purple") Add_Sheet CStr(item) Next End Sub Private Sub Add_Sheet(sName As String) Dim ws As Worksheet On Error Resume Next Set ws = Worksheets(sName) If Err.Number < 0 Then Err.Clear Set ws = Worksheets.Add ws.Name = sName End If Set ws = Nothing On Error GoTo 0 End Sub The Add_Sheet procedure sets the error trap then tries to assign a specific sheet to the variable WS. If it fails, the sheet doesn't exist , so the proc adds it and names it. Patrick Molloy Microsoft Excel MVP -----Original Message----- Windows 2k Pro Excel 2000 I have a Workbook on which I need to do a relatively simple check using VBA code. I want to scan the Workbook for four specific Worksheets (for example the worksheets are named "Red", "Blue", "Green" and "Purple"). If any of these are missing then I'd like to create a new Worksheet with the missing name(s), AND I'd like to delete any Worksheets that are NOT named with one of the four mentioned names. I'm pretty sure I could do this, but I'm afraid my method would not be very efficient at all. How many steps could this be done in? Or, what is the most efficient way to do this? TIA! -gk- . |
All times are GMT +1. The time now is 08:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com