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
Add a comment
|
1 Answer
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.– HowardCommented Apr 27, 2015 at 4:06
-
@Howard - See if it works for you now. I have added the xTitleId declaration part.– PrasannaCommented Apr 27, 2015 at 5:13
-
It made it down to For "i" compile error/variable not defined– HowardCommented Apr 28, 2015 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– PrasannaCommented Apr 28, 2015 at 2:49
-