XL VBA adding new worksheet if current worksheet is not empty
 

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

XL VBA adding new worksheet if current worksheet is not empty
 

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 befo=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

"Lance Hoffmeyer" wrote in message ...
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

XL VBA adding new worksheet if current worksheet is not empty
 

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

On Thu, 26 Aug 2004 19:49:50 -0700, Jim Cone wrote:

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 befo=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

"Lance Hoffmeyer" wrote in message ...
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

XL VBA adding new worksheet if current worksheet is not empty SOLVED
 

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

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

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

On Thu, 26 Aug 2004 19:49:50 -0700, Jim Cone wrote:

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 befo=ActiveSheet, Count:=1 On Error Resume Next
'Name the sheet
ActiveSheet.Name = Application.Input("What is the Worksheet Name")
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

"Lance Hoffmeyer" wrote in message
...
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

XL VBA adding new worksheet if current worksheet is not empty
 

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(Befo=ActiveSheet, Count:=1).Name = vResult
End If
End Sub




In article ,
Lance Hoffmeyer wrote:

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?