Create new sheets and name based off cell value

[288enzo]

I have a bit of code -

strArea = InputBox("Area? (80,82,83,84,87)")
strQtr = InputBox("Quarter? (1,2,3,4)")
    If strArea = "80" Then
        Area80 strQtr
    ElseIf strArea = "82" Then
        Area82 strQtr
    ElseIf strArea = "83" Then
        Area83 strQtr
    ElseIf strArea = "84" Then
        Area84 strQtr
    ElseIf strArea = "87" Then
        Area87 strQtr
    End If

I need some help adding in something like the following as an example. I can't figure out how to get the formula to work with Sheets.Add.Name.

    If strArea = "80" Then
        Area80 strQtr
Create four new worksheets
Sheets.Add.Name = "first "& =Left('Dealer Status'!B4,2)
Sheets.Add.Name = "second" & =Left('Dealer Status'!B4,2)
Sheets.Add.Name = "third" & =Left('Dealer Status'!B4,2)
Sheets.Add.Name = "fourth" & =Left('Dealer Status'!B4,2)
    ElseIf strArea = "82" Then
        Area82 strQtr
Create eight new worksheets
Sheets.Add.Name = "first "& =Left('Dealer Status'!C5,3)
Sheets.Add.Name = "second" & =Left('Dealer Status'!C5,3)
Sheets.Add.Name = "third" & =Left('Dealer Status'!C5,3)
Sheets.Add.Name = "fourth" & =Left('Dealer Status'!C5,3)
Sheets.Add.Name = "first "& =Left('Dealer Status'!C5,14)
Sheets.Add.Name = "second" & =Left('Dealer Status'!C5,14)
Sheets.Add.Name = "third" & =Left('Dealer Status'!C5,14)
Sheets.Add.Name = "fourth" & =Left('Dealer Status'!C5,14)

Thank you as always for everyone's generosity.

 

[Joe4]

This:

Sheets.Add.Name = "first "& =Left('Dealer Status'!B4,2)

should look like this:

Sheets.Add.Name = "first " & Left(Sheets("Dealer Status").Range("B4"), 2)

Also, instead of using all those "ElseIf" statements, just use a Case statement, i.e. this structure:

    If strArea = "80" Then
        Area80 strQtr
    ElseIf strArea = "82" Then
        Area82 strQtr
    ElseIf strArea = "83" Then
        Area83 strQtr
    ElseIf strArea = "84" Then
        Area84 strQtr
    ElseIf strArea = "87" Then
        Area87 strQtr
    End If

can be simplified to this:

    Select Case strArea
        Case "80"
            Area80 strQtr
        Case "82"
            Area82 strQtr
        Case "83"
            Area83 strQtr
        Case "84"
            Area84 strQtr
        Case "87"
            Area87 strQtr
    End Select

See: MS Excel: How to use the CASE Statement (VBA)

 

[288enzo]

I found the following, but get an error Variable not defined.

'VBA Add New Worksheet and Specify Name from Cell
Sub VBAF1_Add_Sheet_Specify_Name_From_Cell()
     
    'Specify Name to Sheet from cell
    Sheets.Add.Name = Sheet1.Range("B1")

End Sub

Source = Create New Worksheet in a Workbook VBA Create Worksheet in Excel

This:

Sheets.Add.Name = "first "& =Left('Dealer Status'!B4,2)

should look like this:

Sheets.Add.Name = "first " & Left(Sheets("Dealer Status").Range("B4"), 2)

Also, instead of using all those "ElseIf" statements, just use a Case statement, i.e. this structure:

    If strArea = "80" Then
        Area80 strQtr
    ElseIf strArea = "82" Then
        Area82 strQtr
    ElseIf strArea = "83" Then
        Area83 strQtr
    ElseIf strArea = "84" Then
        Area84 strQtr
    ElseIf strArea = "87" Then
        Area87 strQtr
    End If

can be simplified to this:

    Select Case strArea
        Case "80"
            Area80 strQtr
        Case "82"
            Area82 strQtr
        Case "83"
            Area83 strQtr
        Case "84"
            Area84 strQtr
        Case "87"
            Area87 strQtr
    End Select

See: MS Excel: How to use the CASE Statement (VBA)

Thank you so very much. I knew I was close
Also, thank you for the tip with using Case instead of If.

 

[Joe4]

You are welcome.

I found the following, but get an error Variable not defined.

If you had deleted the first sheet in the workbook at some point, you would no longer have "Sheet1", and would get that error message,