PDA

View Full Version : Creating multiple sheet along with suffix or prefix assign as per user defined



paradise
10-28-2013, 04:36 AM
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
10-28-2013, 05:54 AM
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
10-28-2013, 07:44 AM
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
10-28-2013, 09:32 AM
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
10-28-2013, 09:39 AM
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
10-28-2013, 10:57 AM
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
10-28-2013, 12:09 PM
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
10-28-2013, 09:51 PM
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
10-29-2013, 06:27 AM
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
10-29-2013, 07:52 AM
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
10-29-2013, 03:50 PM
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
10-29-2013, 09:14 PM
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
10-30-2013, 12:27 PM
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
10-31-2013, 09:41 AM
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.