Add sheets using macro
 

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

Add sheets using macro
 

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

Add sheets using macro
 

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

Add sheets using macro
 

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

Add sheets using macro
 

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

Add sheets using macro
 

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

Add sheets using macro
 

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

Add sheets using macro
 

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

Add sheets using macro
 

Heine,

try this one:

Sub SheetInsert()

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

Sheets.Add
boolFound = False
strNameSheet = "Raabalance"
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

best regards
Gert

Add sheets using macro
 

Thanks Gert - that is better.

Only one problem left as I see it. If I keep running the macro it keeps
adding new sheets called sheet 6,7,8 etc. I would like those sheet to
be deleted or not to be added in the first place. Any ideas?


best regards

Heine
Gert wrote:
Heine,

try this one:

Sub SheetInsert()

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

Sheets.Add
boolFound = False
strNameSheet = "Raabalance"
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

best regards
Gert

Add sheets using macro
 

Sorry Heine, it was my faulth,
it depends on the moment when you add the new sheet.
I've replaced the line Sheets.Add and now it only adds a sheet when
"Raabalance" isn't in use allready

Sub SheetInsert()

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

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


hopes this works as you wanna have it
best regards
Gert

Add sheets using macro
 

That works like a charm, Gert. Thanks so much for taking your time to
help. Wish I were better at writing these codes:-)


Best Regards

Heine
Gert wrote:
Sorry Heine, it was my faulth,
it depends on the moment when you add the new sheet.
I've replaced the line Sheets.Add and now it only adds a sheet when
"Raabalance" isn't in use allready

Sub SheetInsert()

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

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


hopes this works as you wanna have it
best regards
Gert