Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find and unlink current worksheet from old worksheet | Excel Discussion (Misc queries) | |||
Adding a worksheet to a current workbook | Excel Discussion (Misc queries) | |||
Adding a worksheet to a current workbook | Excel Discussion (Misc queries) | |||
'Save current worksheet'; 'Open next worksheet' - two command buttons | Excel Discussion (Misc queries) | |||
I want in one worksheet to relatively link to/reference cells in another without changing the format of the current worksheet. | Excel Discussion (Misc queries) |