XL VBA adding new worksheet if current worksheet is not empty

L

Lance Hoffmeyer

I wish to create some code that will create a new
worksheet if the current worksheet is not empty.

Is this an easy thing to do? I don't have a clue
where to start.

Lance
 
J

Jim Cone

Lance,

See if the following makes sense. It does work...
'-------------------------------------------------------------
'Jim Cone August 26, 2004

Sub AddNewSheetIfNeeded()

'Call function to check for any data in active sheet
If Not GetBottomRow = 0 Then
Application.ScreenUpdating = False
'If necessary, add new sheet before the current sheet.
Worksheets.Add before:=ActiveSheet, Count:=1
On Error Resume Next
'Name the sheet
ActiveSheet.Name = " Lance Did It"
On Error GoTo 0
Application.ScreenUpdating = True
End If

End Sub

'========================================
' GetBottomRow() Function
' Called by AddNewSheetIfNeeded.
' Returns the number of the last worksheet row with data.
' Returns 0 if the sheet is blank.
'========================================
Private Function GetBottomRow() As Long
On Error GoTo NoRow
'Search the entire sheet for any data ("*" is a wildcard)
GetBottomRow = Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Exit Function
NoRow:
GetBottomRow = 0
End Function
'-------------------------------------------------

Regards,
Jim Cone
San Francisco, CA
 
L

Lance Hoffmeyer

Makes perfect sense. It was the GETBOTTOMROW = 0 that was giving me grief.

How would I have Activesheet.Name =

create a popup box so I could name it through something like a msgbox?


Lance

Is there a way
 
L

Lance Hoffmeyer

On Thu, 26 Aug 2004 23:27:55 -0500, Lance Hoffmeyer wrote:

= ActiveSheet.Name = Application.Input("What is the Worksheet Name")
 
J

JE McGimpsey

A similar method, but without the need for a second function:

Public Sub AddNewSheetIfNeeded()
Dim vResult As Variant
Dim shSheet As Worksheet
If Not Application.CountA(ActiveSheet.Cells) = 0 Then
Do
vResult = Application.InputBox( _
Prompt:="New sheet's name: ", _
Title:="Name sheet", _
Type:=2)
If vResult = False Then Exit Sub 'User cancelled
On Error Resume Next
Set shSheet = Sheets(vResult)
On Error GoTo 0
If Not shSheet Is Nothing Then
MsgBox "That name already exists"
vResult = ""
Set shSheet = Nothing
End If
Loop Until vResult <> ""
Application.ScreenUpdating = False
Worksheets.Add(Before:=ActiveSheet, Count:=1).Name = vResult
End If
End Sub
 
Top