create worksheet with specific name

V

Vladimir

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.
 
J

Joel

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
 
A

AndrewCerritos

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 before:=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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top