Creating multiple sheet along with suffix or prefix assign as per user defined

[paradise]

Dear Sir,

Currently,I want to create more than 1 sheet in ms excel say in some work book I require 40 & some 50,etc.I want a macro or vba code along with steps so that it ask me how many sheet do you want to create ? Secondly it should ask me to Suffix or Prefix so that I can add along with Sheet.So that it might be say suffix is 'M' then it should be M1,M2,M3,M4,etc......& if kept prefix then 2M,3M,etc.
Pls note that,Suffix or Prefix will be in Sheet suppose "suresh!A2:A42","Suresh!B2:B42" respectively.That should be linked with Sheet Name(1,2,3,4,5,6 & so on) which is to be created either prefix or suffix.

I think it is possible thru VBA for excel .

I would be very much thankful.

With Best Rgds,
Suresh

[Paul_Hossler]

I was following the request up until this.

Pls note that,Suffix or Prefix will be in Sheet suppose "suresh!A2:A42","Suresh!B2:B42" respectively.That should be linked with Sheet Name(1,2,3,4,5,6 & so on) which is to be created either prefix or suffix.

Please expand

What if the sheet already exists? Just replace it?

Paul

[paradise]

Hi, You may create a drop down which to select-Prefix or suffix and accordingly, it will add prefix & suffix.There will be few sheets present.These sheets should not be replaced.Only on running a vba code it will add in the existing workbook with existing sheet. With Best Rgds, Suresh

[mikerickson]

In names like M1, M2, M3, M is the prefix and 1,2,3 is the index. In 1B, 2B, 3B, B is the suffix.

It sounds like you want the user to
1) Enter how many sheets to add
2) Select a prefix/suffix from a list of options on a worksheet
3) Enter whether the selection in #2 is to be used as a prefix or a suffix

This routine will do that. (Adjust the sheet name and range of cells holding allowable prefixes/suffixes to suit your situation.)

Controlling which workbook the sheets are added to is not a feature of this routine. It will have to be modified to give that option.

Sub test()
    Dim strFix As String, asPrefix As Boolean
    Dim uiFixStringRange As Range, uiNewCount As Long
    Dim PreAddSheetCount As Long, i As Long
    Dim strPrompt As String
    
    uiNewCount = Int(Application.InputBox("How many new sheets to add", Default:=3, Type:=1))
    If uiNewCount < 1 Then Exit Sub: Rem cancel pressed
    
    With ThisWorkbook.Sheets("PrePostList").Range("A1:A5,C1:C3"): Rem adjust
        strPrompt = "Select the cell that holds the pre/post fix that you want from " & .Address(False, False)
        Do
            Application.Goto .Cells
            .Cells(1, 1).Activate

            On Error Resume Next
            Set uiFixStringRange = Application.InputBox(strPrompt, Default:=.Range("A1").Address(False, False, , True), Type:=8).Cells(1, 1)
            On Error GoTo 0

            If uiFixStringRange Is Nothing Then Exit Sub: Rem cancel pressed
            If Application.Intersect(.Cells, uiFixStringRange) Is Nothing Then MsgBox "Cell selected is out of allowable range."
        Loop While Application.Intersect(.Cells, uiFixStringRange) Is Nothing
    End With
    strFix = CStr(uiFixStringRange.Value)
    
    strPrompt = "Yes, I want prefixes (e.g. " & strFix & "1, " & strFix & "2, " & strFix & "3, ...)."
    strPrompt = strPrompt & vbCr & "No, I want sufixes (e.g. 1" & strFix & ", 2" & strFix & ", 3" & strFix & ", ...)."
    Select Case MsgBox(strPrompt, vbYesNoCancel)
        Case vbCancel: Exit Sub
        Case vbYes: asPrefix = True
        Case vbNo: asPrefix = False
    End Select
    
    With ThisWorkbook: Rem add sheets to this workbook
        PreAddSheetCount = .Sheets.Count
        .Worksheets.Add after:=.Sheets(PreAddSheetCount), Count:=uiNewCount
        On Error Resume Next
        For i = 1 To uiNewCount
            If asPrefix Then
                .Sheets(PreAddSheetCount + i).Name = strFix & i
            Else
                .Sheets(PreAddSheetCount + i).Name = i & strFix
            End If
        Next i
        On Error GoTo 0
    End With
End Sub

[Paul_Hossler]

Still not understand some things, but this will add numbered sheets based on how many you want, a base name, and if you want the number at the front as prefix or back as a suffix

Example:

10 sheets

base name = "MyData"

Prefix = False

generates 10 sheets named "MyData01", ... "MyData10"

Option Explicit
Sub AddSheets()
    Dim sBase As String
    Dim bPrefix As Boolean
    Dim iHowMany As Long, i As Long
    Dim sSheetName As String
    
    iHowMany = Application.InputBox("Number of Sheets to add, '0' to exit", "AddSheets", , , , , , 1)
    If iHowMany = 0 Then Exit Sub
    
    sBase = Application.InputBox("Base name of sheets, blank to exit", "AddSheets", "BASE", , , , , 2)
    If Len(sBase) = 0 Then Exit Sub
    bPrefix = Application.InputBox("TRUE for Prefix (01ABCD), or" & vbCrLf & "FALSE for Suffix (ABCD01)", "AddSheets", CStr(True), , , , , 4)

    For i = 1 To iHowMany
        If bPrefix Then
            sSheetName = Format(i, "00") & sBase
        Else
            sSheetName = sBase & Format(i, "00")
        End If
        
        On Error Resume Next
        Application.DisplayAlerts = False
        Worksheets(sSheetName).Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        
        Call Worksheets.Add(, ActiveSheet)
        ActiveSheet.Name = sSheetName
    
    Next i
End Sub

If you're looking for something else, then you'll need to provide better and more complete examples

Paul

[paradise]

Dear Sir,

I have enclosed revised workbook.In Sheet "PrepostList",I want my Sheet name to be M1,M2,etc which is in Column C from C2:C33 or my Sheet name to be 1S,2S,etc which is in Column D.

Also, in addition to it,I want to link B2:B32 Data at each B1 corresponding to the data i Column C or D

I have shown a sample by creating manually of Sheet "M1" with corresponding data in B2.

I think this is sufficient & hope you have understood.

With Best Rgds,
Suresh

[mikerickson]

I think this will do what you want.

Sub MakeSheets()
    Dim uiOption As Range
    Dim NameRange As Range
    Dim lastOld As Long, i As Long
    
    On Error Resume Next
    Sheet1.Activate
    Set uiOption = Application.InputBox("click on Suffix or Prefix column", Type:=8)
    On Error GoTo 0
    If uiOption Is Nothing Then Exit Sub
    If uiOption.Column <> 3 And uiOption <> 4 Then MsgBox "click on column C or D": Exit Sub
    
    With uiOption.Columns(1).EntireColumn
        Set NameRange = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    With ThisWorkbook
        lastOld = .Sheets.Count
        .Worksheets.Add after:=.Sheets(lastOld), Count:=NameRange.Cells.Count
        On Error Resume Next
        For i = 1 To NameRange.Cells.Count
            With .Sheets(i + lastOld)
                .Name = CStr(NameRange.Cells(i, 1).Value)
                .Range("B1").FormulaR1C1 = "=" & NameRange.Cells(i, 1).EntireRow.Range("B1").Address(False, False, xlR1C1, True, .Range("B1"))
            End With
        Next i
        On Error GoTo 0
    End With
    
End Sub

[paradise]

Hi mikerickson,

Thanks for the reply.

I have duly checked,it did work well to Suffix but for Prefix I am getting a Run time error '13' & Type Mismatch.

In addition to above,it has not asked how many sheets would you like to create ? This should be there.Suppose,I assign it to be 20 then 20 sheets should be created accordingly in the manner for prefix & suffix which is done by you currently in the sheet instead of all that is being listed here in Column B & C data.



With Best Rgds,

[mikerickson]

The lists on your attached sheet has only 20 entries for either Prefix or Suffix, does that set a maximum for the number of added sheets?
If not, what should one name the 21st sheet?

[paradise]

There might be in reality more than 20 sheets but there should be an option for asking how many sheets? If the entry is there then it will create as per the user defined input,if not then minimum sheets as per entry.

Hope you have understood.

With Best Rgds

[mikerickson]

So the routine should add either the number of sheets that the user specifies OR the number of rows in the suresh! sheet, whichever is smaller?

[paradise]

So the routine should add either the number of sheets that the user specifies OR the number of rows in the suresh! sheet, whichever is smaller?

Let me explain you more clearly,
1st macro or vba code

Suppose if in Suresh!sheet no.of rows is 20, if user input 20,the accordingly,it should creates 20 sheets
" same as above " , if user input 10 then 10 sheets should be created becoz in suresh! sheet we have 20 rows entry
" same as above " ,if user input 30 then 20 sheets should be created becoz in suresh! sheet we have only 20 rows entry

2nd macro or vba code for further below changes:

After entry,and after few days suppose further that a user does a entry in further rows in suresh sheet,hence it should prompt a user 'do you want to create further sheet for changes in that specific rows".

Hope this would be suffice info.

With Best Rgds,

[mikerickson]

If the user enters 30, the sheet supplies the names of the first 20 new sheets, what should the names of the last 10?

[paradise]

The result will be displayed of 20 sheets only becoz extra 10 sheet there is no entry however the input is done for 30 sheets.Hence there will be no name of the last 10 sheets becoz there is no entry.