Hi,

I want to add sheet and call it a name. But when I record it a problem
occurs. Everytime I add a sheet it is given a new name i.e. sheet 2,
sheet 3, sheet 4 etc. This happens before I get a chance to rename
which means the macro always fails.

Any thoughts?

I want to add a new sheet to at lot of workbooks - the sheet is called
"Raabalance"

/Heine

Try an inputbox (or replace the inputboxline with a fixed name)

Sub SheetInsert()

Dim strNameSheet As String

Sheets.Add
strNameSheet = InputBox("give sheet name")
ActiveSheet.Name = strNameSheet

End Sub

Thanks - that works great.

Problem is now that if I run the macro more than once an error occurs
because I already added the sheet once. Is there an easy way to get
around that small problem?

/Heine
Gert wrote:
Try an inputbox (or replace the inputboxline with a fixed name)

Sub SheetInsert()

Dim strNameSheet As String

Sheets.Add
strNameSheet = InputBox("give sheet name")
ActiveSheet.Name = strNameSheet

End Sub

worksheets.Add.name="Bob"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Heine" wrote in message
ups.com...
Hi,

I want to add sheet and call it a name. But when I record it a problem
occurs. Everytime I add a sheet it is given a new name i.e. sheet 2,
sheet 3, sheet 4 etc. This happens before I get a chance to rename
which means the macro always fails.

Any thoughts?

I want to add a new sheet to at lot of workbooks - the sheet is called
"Raabalance"

/Heine

I thought to add a worksheet one used the workbooks.add method.
Confused..
Jim

"Bob Phillips" wrote in message
:

worksheets.Add.name="Bob"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Heine" wrote in message
ups.com...
Hi,

I want to add sheet and call it a name. But when I record it a problem
occurs. Everytime I add a sheet it is given a new name i.e. sheet 2,
sheet 3, sheet 4 etc. This happens before I get a chance to rename
which means the macro always fails.

Any thoughts?

I want to add a new sheet to at lot of workbooks - the sheet is called
"Raabalance"

/Heine

Never mind; just having a "senior-moment".. daaaa

"Jim May" wrote:

I thought to add a worksheet one used the workbooks.add method.
Confused..
Jim

"Bob Phillips" wrote in message
:

worksheets.Add.name="Bob"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Heine" wrote in message
ups.com...
Hi,

I want to add sheet and call it a name. But when I record it a problem
occurs. Everytime I add a sheet it is given a new name i.e. sheet 2,
sheet 3, sheet 4 etc. This happens before I get a chance to rename
which means the macro always fails.

Any thoughts?

I want to add a new sheet to at lot of workbooks - the sheet is called
"Raabalance"

/Heine

the extended version

Sub SheetInsert()


Dim strNameSheet As String
Dim boolFound As Boolean
Dim MySheets As Worksheet

Sheets.Add
boolFound = False
strNameSheet = InputBox("give sheet name")
For Each MySheets In Worksheets
If MySheets.Name = strNameSheet Then boolFound = True
Next
If boolFound Then
MsgBox ("this sheet already exists")
Else
ActiveSheet.Name = strNameSheet
End If

End Sub

Thanks Gert,

that´s quite crafty. One or two minor details, though:

I would like, if possible to avoid the use of an inputbox.
The macro still adds sheets called sheet 5, sheet 6, sheet 7 etc - can
I avoid this problem?

/Heine
Gert wrote:
the extended version

Sub SheetInsert()


Dim strNameSheet As String
Dim boolFound As Boolean
Dim MySheets As Worksheet

Sheets.Add
boolFound = False
strNameSheet = InputBox("give sheet name")
For Each MySheets In Worksheets
If MySheets.Name = strNameSheet Then boolFound = True
Next
If boolFound Then
MsgBox ("this sheet already exists")
Else
ActiveSheet.Name = strNameSheet
End If

End Sub