0

Sub CreateWorkSheetByRange()
Dim WorkRng As Range
Dim Ws As Worksheet
 Dim arr As Variant
Dim template As Worksheet
Dim xTitleId As String
On Error Resume Next
xTitleId = "Select Range"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
arr = WorkRng.Value
Application.ScreenUpdating = False
 arr = WorkRng.Value
 Application.ScreenUpdating = False
' Create the Worksheet names based on range selected 
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(arr, 2)
Set Ws = Worksheets.Add(after:=Application.ActiveSheet)
  Ws.Name = arr(i, j)
   Next
Next
Application.ScreenUpdating = True
End Sub

0

You can certainly create worksheet names using the values from Excel Cells.
Use the following VBA code

Sub CreateWorkSheetByRange()
'variable declaration
Dim WorkRng As Range
Dim Ws As Worksheet
Dim arr As Variant
Dim template As Worksheet
'**Edit-2:Declared xTitleId**
Dim xTitleId As String
'**Edit-3:Declared i and j**
Dim i as Variant
Dim j as Variant
'Start of Program
On Error Resume Next
'Specify the title of the dialog that requests for range
xTitleId = "Select Range"
' Assign the application.selection function to the variable WorkRng
Set WorkRng = Application.Selection
' Accept input from the user
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
' Create an array of the input values
arr = WorkRng.Value
' The following line is optional
Application.ScreenUpdating = False
' Create the Worksheet names based on range selected
For i = 1 To UBound(arr, 1)
    For j = 1 To UBound(arr, 2)
        Set Ws = Worksheets.Add(after:=Application.ActiveSheet)
        Ws.Name = arr(i, j)
    Next
Next
Application.ScreenUpdating = True
End Sub

The above code allow you to select a range of cells from which you would want to create the names of the worksheets.
You can further build on this and see if you can modify this for your changing names scenario
FYI: I'm new to VBA too... but you can learn this very easily

  • Thank you for taking time help me, I'm still having troubles I cut and past your cod in new module and tried running it. it stops at xTitleId with message "Compile Error" & "variable not defined" I'm sure this is my lack of knowledge but is there additional steps need to get this to work? Thank you again. – Howard Apr 27 '15 at 4:06
  • @Howard - See if it works for you now. I have added the xTitleId declaration part. – Prasanna Apr 27 '15 at 5:13
  • It made it down to For "i" compile error/variable not defined – Howard Apr 28 '15 at 1:24
  • @Howard, can you please add your code as a EDIT in your own question - it would be easy to comment on where it could be wrong – Prasanna Apr 28 '15 at 2:49
  • I'm thinking I may be taking on more then I'm ready for. – Howard Apr 28 '15 at 3:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.