Excel

Work with a Specific Worksheet that May or May Not Already Exist

Ease of Use

Easy

Version tested with

2002, 2003 

Submitted by:

Jacob Hilderbrand

Description:

This macro shows how to work with a specific worksheet that may or may not exist. If the worksheet already exists then you can work with that worksheet, if the worksheet does not exist, the macro will create it. 

Discussion:

Sometimes you may need to work with a specific worksheet, however, that worksheet may or may not have been created previously. This macro shows how you can deal with both cases and create the worksheet if it does not exist already. 

Code:

instructions for use

			

Option Explicit Sub MakeWorksheet() Dim wsName As String Dim ws As Worksheet wsName = ThisWorkbook.Sheets("Sheet1").Range("A1").Text On Error Resume Next Set ws = Sheets(wsName) If Err <> 0 Then Set ws = Sheets.Add ws.Name = wsName ws.Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) End If On Error GoTo 0 With ws 'Code here to work with the worksheet. End With End Sub

How to use:

  1. Open Excel.
  2. Alt + F11 to open the VBE.
  3. Insert | Module.
  4. Paste the code from above in the code window that opens up.
  5. Close the VBE (Alt + Q or press the X in the top-right corner).
 

Test the code:

  1. Tools | Macro | Macros...
  2. Select MakeWorksheet and press Run.
 

Sample File:

MakeWorksheet.ZIP 7.35KB 

Approved by mdmackillop


This entry has been viewed 109 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express