CODEWITHSUNDEEP
vbaexcel
maniA
maniA

Reputation: 1457

Read cell for cell and create sheets

How can I read in Visual Basic from column B, sheet "control" in Excel cell for cell till an empty cell?

After that I would like to generate for every cell a new sheet with the name from cells. In this:

picture:

you see the content of this column, which could be different from time to time. After reading it I want to generate empty sheets with names: RW_BONDS, ... .

Upvotes: 1

Views: 100

Answers (4)

MatthewD
MatthewD

Reputation: 6801

You can do something like this.

Private Sub CommandButton1_Click()
Dim ws As Excel.Worksheet
Dim lRow As Long
Dim lastRow  As Long

    'Set the sheet to read from
    Set ws = Application.Sheets("control") 

    'Set the row to start reading at
    lRow = 3

    lastRow = wsOwners.Cells(wsOwners.Rows.Count, "B").End(xlUp).Row

    'Loop through the rows
    Do While lRow <= lastRow

        If ws.Range("B" & lRow).Value <> "" then

            'Add a new sheet
            ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)

            'Change the name to the value of column B in the current row
            ActiveWorkbook.ActiveSheet.Name = ws.Range("B" & lRow).Value

        End If

    'Increment your row to the next one
    lRow = lRow + 1

    Loop

End Sub

Upvotes: 2

Chris Geatch
Chris Geatch

Reputation: 113

Sub createSheets()
    With Worksheets("control")
        iRow = 1                            'Start on the first row
        While Len(.Cells(iRow, 2)) > 0      'While there isn't a blank cell
            Worksheets.Add.Name = .Cells(iRow,2) 'Create/rename sheet
            iRow = iRow + 1
        Wend
    End With
End Sub

Upvotes: 1

Dirk Reichel
Dirk Reichel

Reputation: 7979

Sub test()
  Dim i As Long
  i = 1
  While Len(Sheets("Control").Cells(i, 2))
    Worksheets.Add.Name = Sheets("Control").Cells(i, 2): i = i + 1
  Wend
End Sub

EDIT answer for the comment:

Sub test()
  Dim i As Long
  i = 1
  With Sheets("Control")
    On Error Resume Next
    Application.DisplayAlerts = False
      While Len(.Cells(i, 2))
      If Len(Sheets(.Cells(i, 2).Value).Name) = 0 Then Else Sheets(.Cells(i, 2).Value).Delete
      Worksheets.Add.Name = .Cells(i, 2): i = i + 1
    Wend
  Application.DisplayAlerts = True
  On Error GoTo 0
  End With
End Sub

Upvotes: 1

elzell
elzell

Reputation: 2306

set ws = worksheets("Source")

row = 1
col = "B"

Do
  row = row + 1
  if ws.range(col & row).text = "" then exit do
  worksheets.add.name = ws.range(col & row).text
Loop

End Sub

Upvotes: 1

Related Questions