Naming worksheets in loop

[Guest]

Hi! I am writing a macro for excel that generates new worksheets. I want to
name the worksheets so that all worksheets has one part of their name that is
the same for all worksheets and I want to have one variable part. My erronous
code now is:

Set NewSheet = Worksheets.Add
NewSheet.Name = "My worksheet" + x

I want to have a counter for x but I do not know how to comine the String
part ("My worksheet") with the value of the variabel so that the final name
is e.g. My worksheet 14. Help please!

 

[Guest]

Jenni, you can use concatenation:

"My Worksheet " & x

 

[Alan]

NewSheet.Name = "My worksheet " & Str(x)

Regards,

 

[Norman Jones]

Hi Jenni,

Try:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim iCtr As Long
Const sName As String = "MyWorksheet" '<<==== CHANGE

Set WB = Workbooks("YourBook.xls") '<<==== CHANGE

iCtr = WB.Worksheets.Count

Set SH = Worksheets.Add(after:=Worksheets(iCtr))

SH.Name = sName & iCtr

End Sub
'<<=============