#1
|
|||
|
|||
Macro to conditionally create or go to worksheet
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 |
#2
|
||||
|
||||
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 |
Tags |
conditional, worksheet |
|
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 |