Macro to conditionally create or go to worksheet

Reinaldo123 · #1 07-03-2012, 12:24 PM

Hi,

I have a budget workbook for logging staff expenses that uses a macro to create either a travel, conference, or consulting fee worksheet when the associated button is clicked. The new worksheet has the name of the staff member and type of expense in abbreviated form.

If the staff member has already created their specific worksheet, then they would click on a button from the main worksheet that would take them to directly to that worksheet to amend.

I'd like to consolidate this so that one button for each type will either create the worksheet if it doesn't exist or just go directly to that sheet. Can anyone recommend what would be the best way to check on the existence of a worksheet and, if not found, then to create it?

Thanks,
Reinaldo

Colin Legg · #2 07-06-2012, 07:23 AM

Hi,

Here's one way:

Code:

Sub Main()
    
    Dim wstNew As Worksheet
    
    If WorksheetExists(WhichWorkbook:=ThisWorkbook, WorksheetName:="My New Worksheet") Then
        ThisWorkbook.Activate
        ThisWorkbook.Worksheets("My New Worksheet").Select
    Else
        With ThisWorkbook.Worksheets
            Set wstNew = .Add(After:=.Item(.Count))
            wstNew.Name = "My New Worksheet"
        End With
    End If
    
End Sub

Function WorksheetExists(ByVal WhichWorkbook As Workbook, ByRef WorksheetName As String) As Boolean
    On Error Resume Next
        
    WorksheetExists = Not WhichWorkbook.Worksheets(WorksheetName) Is Nothing
End Function

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Create/delete New worksheet	ibrahimaa	Excel Programming	5	01-16-2012 11:53 PM
How to create a system folder with VB macro?	Joe Patrick	Word VBA	4	12-17-2011 02:04 PM
Create Custom menu using a macro	twnty2	PowerPoint	0	06-29-2011 04:26 PM
How to create macro to paste text after style?	Srivas	Word	0	03-16-2010 05:28 AM
macro for comparing data from 3 columns and pasting into another worksheet	ashukla	Excel	1	06-24-2009 05:01 PM