VBA Express : Excel - Work with a Specific Worksheet that May or May Not Already Exist

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:

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

Test the code:

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

Sample File:

MakeWorksheet.ZIP 7.35KB

Approved by mdmackillop

This entry has been viewed 115 times.