create worksheet with specific name
 

The workbook has already 4 worksheets: Plan and 3 reports.
In the first worksheet (plan) with a toolbox combobox the user will choose 1
of 4 options:
Jan-feb-march
apr-may-june
july-aug-sept
oct-nov-dec

when 1 of 4 options is choosen automatically other 3 worksheets shoud
receive respective names: say apr, may, june and in a cell of each worksheet
should appear April, May or June correspondingly.

create worksheet with specific name
 

MonthSelected = combobox1.Value

For Each sht In Sheets
If UCase(sht.Name) < "PLAN" Then

'test if there is a dash in sht names
If InStr(MonthSelected, "-") 0 Then
Mnth = Left(MonthSelected, _
InStr(MonthSelected, "-") - 1)
sht.Name = Mnth
MonthSelected = Mid(MonthSelected, _
InStr(MonthSelected, "-") + 1)
Else
sht.Name = MonthSelected
Exit For
End If
End If
Next sht


"Vladimir" wrote:

The workbook has already 4 worksheets: Plan and 3 reports.
In the first worksheet (plan) with a toolbox combobox the user will choose 1
of 4 options:
Jan-feb-march
apr-may-june
july-aug-sept
oct-nov-dec

when 1 of 4 options is choosen automatically other 3 worksheets shoud
receive respective names: say apr, may, june and in a cell of each worksheet
should appear April, May or June correspondingly.

create worksheet with specific name
 

Hi, is this what you need?
1) this sub will be called when user select something in the ComboBox
2) the value is parsed, separated by "-"
3) active sheet is the PLAN sheet: move it to the first
4) name the rest of sheets by month name
5) also put month name to cell [A1] in corresponding sheet
If full name is needed (i.e. April instead of apr), can add simple
translation routine to do it.

Private Sub ComboBox1_Change()
Dim strQTR As String

strQTR = ComboBox1.Value
ActiveSheet.Move befo=Worksheets(1) ' move PLAN as 1st sheet
Worksheets(2).Name = Split(strQTR, "-")(0) ' 1st month
Worksheets(3).Name = Split(strQTR, "-")(1) ' 2nd month
Worksheets(4).Name = Split(strQTR, "-")(2) ' 3rd month
Worksheets(2).[A1] = Worksheets(2).Name ' cell [A1] has the name
Worksheets(3).[A1] = Worksheets(3).Name
Worksheets(4).[A1] = Worksheets(4).Name
End Sub

--AC


"Joel" wrote:

MonthSelected = combobox1.Value

For Each sht In Sheets
If UCase(sht.Name) < "PLAN" Then

'test if there is a dash in sht names
If InStr(MonthSelected, "-") 0 Then
Mnth = Left(MonthSelected, _
InStr(MonthSelected, "-") - 1)
sht.Name = Mnth
MonthSelected = Mid(MonthSelected, _
InStr(MonthSelected, "-") + 1)
Else
sht.Name = MonthSelected
Exit For
End If
End If
Next sht


"Vladimir" wrote:

The workbook has already 4 worksheets: Plan and 3 reports.
In the first worksheet (plan) with a toolbox combobox the user will choose 1
of 4 options:
Jan-feb-march
apr-may-june
july-aug-sept
oct-nov-dec

when 1 of 4 options is choosen automatically other 3 worksheets shoud
receive respective names: say apr, may, june and in a cell of each worksheet
should appear April, May or June correspondingly.