CODEWITHSUNDEEP
excelvba
NCC
NCC

Reputation: 819

How to create multiple sheets from a list of names which are the selected cells' content

Actually, I only need to help to provide some lessons on Excel VBA not for the code.

Problem:

enter image description here

The main line of code is very basic and although I have no experience in VBA, I understand it

Sheets.Add().Name = Name_of_Sheet

If I define a list of name like NameList =Array("SheetA", "SheetB", "SheetC","SheetD") then do the for loop

 For I = LBound(NameList) To UBound(NameList) 
    Sheets.Add().Name = Tabs(I) 
 Next I

However, many times, there are standard for naming the sheets, going to Visual Basic to edit the macro is not very efficient. I would like to just create the sheet from the cells' content

My questions:

1) How does the index of selected data (1D columns, 1D row, or multiple rows x multiple columns) work?

2) How can I access these cells' content?

Upvotes: 4

Views: 76143

Answers (4)

Froid Andrewson
Froid Andrewson

Reputation: 1

with sheets
     .add.name="SheetA"
     .add.name="SheetB"
     .add.name="Sheetc"
end with

Upvotes: -1

Lena Skalska
Lena Skalska

Reputation: 11

I use this for my needs:

Sub C_CreateEmptySheets()
    Dim MyCell As Range, MyRange As Range

    'This Macro will create separate tabs based on a list in Distribution Tab A2 down

    Set MyRange = Sheets("Distribution").Range("A2")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))


    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
    Next MyCell

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Upvotes: 1

Glenn Langford
Glenn Langford

Reputation: 27

Thanks Doug, this is great. Slight mod to avoid having to re-name the range:

    'select list range before running procedure
Sub AddSheets()
Dim cell As Excel.Range
Dim wbToAddSheetsTo As Excel.Workbook

Set wbToAddSheetsTo = ActiveWorkbook
For Each cell In Selection
    With wbToAddSheetsTo
        .Sheets.Add after:=.Sheets(.Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = cell.Value
        If Err.Number = 1004 Then
          Debug.Print cell.Value & " already used as a sheet name"
        End If
        On Error GoTo 0
    End With
Next cell
End Sub

Upvotes: 1

Doug Glancy
Doug Glancy

Reputation: 27488

Your question is quite open-ended. Here's a start that addresses the "1D Columns" aspect. There are many ways to do this, but I've included a couple of basic VBA constructs, like For Each and With/End With. You could easily point the variables at other workbooks, worksheets or cells. It's got a little error-handling to address trying use a sheet name that already exists:

Sub AddSheets()
Dim cell As Excel.Range
Dim wsWithSheetNames As Excel.Worksheet
Dim wbToAddSheetsTo As Excel.Workbook

Set wsWithSheetNames = ActiveSheet
Set wbToAddSheetsTo = ActiveWorkbook
For Each cell In wsWithSheetNames.Range("A2:A5")
    With wbToAddSheetsTo
        .Sheets.Add after:=.Sheets(.Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = cell.Value
        If Err.Number = 1004 Then
          Debug.Print cell.Value & " already used as a sheet name"
        End If
        On Error GoTo 0
    End With
Next cell
End Sub

Another approach would be to load the cell contents into an array, which might be useful if it was in fact two-dimensional (and if there were tons of names) but it might also be overkill.

Upvotes: 4

Related Questions