Create a new worksheet (excel 2003) if one or more cells in a range is not empty

  • Thread starter Thread starter Luc Ferrari
  • Start date Start date
L

Luc Ferrari

I want to create a new "tab, worksheet or whatever you call it " (in excel
2003) if one or more cells in a range contain a value.
The code starts after i hit a "button".

And name the sheet after the value of the cell left of the first cell of
that range.

I have no clue in how to program this.
Can someone provide me the code for this....


Thanks for your help

Luc
 
I want to create a new "tab, worksheet or whatever you call it " (in excel
2003) if one or more cells in a range contain a value.
The code starts after i hit a "button".

And name the sheet after the value of the cell left of the first cell of
that range.

I have no clue in how to program this.
Can someone provide me the code for this....

Thanks for your help

Luc
Click to expand...

a finite range? ie: cells A1:A20 or an infinite range?

if finite:

sub A()
dim i
for i=1 to 20
if thisworkbook.activeshees.cells(i,1)<>"" then
thisworkbook.sheets.add.name= thisworkbook.sheets.cells(i,
1).value 'or thisworkbook.sheets.cells(i,1).text
end if
next i

this should work.
 
Hi Luc,

"if one or more cells in a range contain a value." What is this range and
what worksheet is it on? or is it simply a user selected range?
 
Hi Luc,

"if one or more cells in a range contain a value." What is this range and
what worksheet is it on? or is it simply a user selected range?

--
Regards,

OssieMac


The range is part of a row , example : B5:V5
The range is always on the same sheet


Regards,
Luc
 
if finite:

sub A()
dim i
for i=1 to 20
if thisworkbook.activeshees.cells(i,1)<>"" then
thisworkbook.sheets.add.name= thisworkbook.sheets.cells(i,
1).value 'or thisworkbook.sheets.cells(i,1).text
end if
next i


Thanks for the reply !
Does this create a worksheet for every cell in the range that contains a
value ? (example : 5 cells have a value => 5 sheets added)
I would achieve the following : Example : if 8 cells in the range contain a
value, then only 1 sheet should be added (never more than 1 sheet should be
added)

Thanxxxx

Luc
 
Hi again Luc,

To add the button in Excel 2007: (see below for other versions)
If Developer Ribbon not displayed then see help under Developer ribbon to
turn it on.
You might also have to see how to set macro security to allow the macro to
run and when you save the workbook, save as a macro enabled workbook (.xlsm)
Developer ribbon -> Insert and Select button from ActiveX controls and then
click on worksheet and hold left mouse button and drag button out to required
size.

To add the button in earlier versions of Excel:
On the Controls Toolbox Toolbar (Not the forms toolbar) Select command
button and then click on worksheet and hold left mouse button and drag button
out to required size.

Note while doing the above the Ribbon or Toolbar Design mode button (Like a
set square, ruler and pencil) is activated (orange color). Leave it like that
for time being.

Right click the new button and select View code.

Copy the below code (between asterisk lines) and paste between the sub and
end sub in the VBA editor.

Click the X with red background top right to close the VBA editor.

Click the Design Mode button to turn it off.

You can now click the button to run your code.

If you want to make changes to the button design then click the Design mode
button and then right click the button and select properties.

Set the Caption property to what you want to display on the button.

To open the VBA editor again use Alt/F11 and then in the project explorer on
the left select the worksheet that has the button (If correct worksheet code
is not already displayed.)

Note that lines in green after copying into the VBA editor are comments
only. Also a space and underscore at the end of a line is a line break in an
otherwise single line of code.

'*************************************
'Uses a specified range to test
'In this example, the worksheet
'is named by value in cell A5
Dim rngToTest As Range
Dim strNewShtName As String
Dim wsNewSht As Worksheet

'Edit "Sheet1" to your sheet name
With Sheets("Sheet1")
'Edit "B5:V5" to your range
Set rngToTest = .Range("B5:V5")
End With

'Assign proposed new worksheet name to variable
strNewShtName = rngToTest _
.Cells(1, 1).Offset(0, -1).Value

'Test for new sheet name already existing
'by attempting to assign to a variable
On Error Resume Next
Set wsNewSht = Sheets(strNewShtName)

'If no error then the attempt to assign to variable
'was successful so worksheet already exists
If Err.Number = 0 Then 'No error
MsgBox "Worksheet " & strNewShtName _
& " already exists"
Exit Sub
End If

On Error GoTo 0 'Resume error trapping ASAP

'If get to here worksheet does not exist
'so create and name
If WorksheetFunction.CountA(rngToTest) > 0 Then
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = _
rngToTest.Cells(1, 1).Offset(0, -1).Value
End If

'Return to original worksheet.
Sheets("Sheet1").Select
Range("A1").Select

'*****************************************

Note that example will fail if the range starts in column A because there is
no cell to the left of the range.
 
Back
Top