Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

Excel VB: Creating a new page with a specific name

With VB in Excel, I'm attempting to create a new page with a specific name but it seems like the internal name given every time is Sheet1, Sheet2, etc.  I also need some VB code to check for existence of this page and delete it.
0
robjay
Asked:
robjay
  • 2
  • 2
1 Solution
 
DennisBorgCommented:
Simple example of adding a WorkSheet to the ActiveWorkbook and specifying its name:

   ActiveWorkbook.Worksheets.Add().Name = "TestMe"

To see if a worksheet exists with a certain name, you can use the following function:

Public Function WorkSheetExists(ByVal wsName As String) As Boolean
   Dim ws As Worksheet
   Dim rv As Boolean   'Return Value
   
   For Each ws In ActiveWorkbook.Worksheets
      If StrComp(wsName, ws.Name, vbTextCompare) = 0 Then
         rv = True
         Exit For
      End If
   Next 'ws
   Set ws = Nothing
   
   WorkSheetExists = rv
End Function


Sample code to delete a WorkSheet:

   ActiveWorkbook.Worksheets("Sheet2").Delete


If you want a single sub to delete a worksheet if it exists, you could use the following along with the WorkSheetExists given earlier:

Public Sub KillWorkSheet(ByVal SheetName As String)
   If WorkSheetExists(SheetName) Then
      ActiveWorkbook.Worksheets(SheetName).Delete
   End If
End Sub


-Dennis Borg
0
 
Richie_SimonettiIT OperationsCommented:
Sub AddNewSheet(SheetName As String)
Dim wsh As Worksheet
For Each wsh In ActiveWorkbook.Sheets
    If wsh.Name = SheetName Then
        wsh.Delete
        Exit For
    End If
Next
Set wsh = ActiveWorkbook.Sheets.Add(Before:=Worksheets(Worksheets.Count))
wsh.Name = SheetName
End Sub
0
 
DennisBorgCommented:
In my previous post, I illustrated how you can perform some different tasks:

   1) Add a sheet with a given name
   2) Delete a sheet
   3) How to tell if a sheet exists
   4) How to "AutoKill" a sheet if it exists (sub KillWorkSheet)


In the event you want to be able to add a sheet with a given name, and if a sheet by that name already exists to simply delete it and then add a new one with that name, you can use the following sub:

Public Sub AddSheet(ByVal SheetName As String)
   On Error GoTo ErrHandler
   
   ActiveWorkbook.Worksheets.Add().Name = SheetName
   Exit Sub
   
ErrHandler:
   Select Case Err.Number
      Case 1004:
         ActiveWorkbook.Worksheets(SheetName).Delete
         Resume
      Case Else:
         With Err
            .Raise .Number, .Source, .Description, .HelpFile, .HelpContext
         End With
   End Select
End Sub
0
 
robjayAuthor Commented:
Thanks! May have some follow up questions as I implement -
0
 
robjayAuthor Commented:
Public Sub AddSheet(ByVal MySheet As String)
On Error GoTo ErrHandler

ActiveWorkbook.Worksheets.Add().Name = "MySheet"
Exit Sub

ErrHandler:
Select Case Err.Number
    Case 1004:
        ActiveWorkbook.Worksheets(MySheet).Delete
        Resume
    Case Else:
    With Err
        .Raise .Number, .Source, .Description, .HelpFile, .HelpContext
    End With
End Select
End Sub

I get an error:  subscript out of range when this is ran with MySheet in existance and highlight the Delete statement.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now