-1

I have a range of cell numbers that I need for multiple worksheet names.

I create multiple worksheets based on the number of rows.

Sub Copier()
    Dim x As Integer
    x = InputBox("Enter number of times to copy worksheet")
    For numtimes = 1 To x
    ActiveWorkbook.Sheets("OMRS 207").Copy _
    After:=ActiveWorkbook.Sheets("OMRS 207")
    Next
End Sub

That grabs only one name, OMRS 207.

I want to generate these worksheets using the entire range of cells in the original worksheet.

Community
  • 1
  • 1
mkerkes
  • 21
  • 1
    Possible duplicate of [Error in finding last used cell in VBA](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba), although it sounds more like a design problem. – Comintern Sep 27 '16 at 15:41
  • You never loop through anything. In your `For` loop, you don't step through anything, so it'll copy the same sheet over and over. – BruceWayne Sep 27 '16 at 15:55
  • We're confused as to what you want. Are you saying that you want the names of the worksheets to be based on the contents of the cells in OMRS 207? If you are creating a sheet for each cell in the source, why ask the user to input the number? If you declare a range in OMRS 207 and then "for each cl in myRange" instead of "for numtimes = 1 to x" then you can change the name of each sheet to cl.text. – Hrothgar Sep 27 '16 at 15:56

1 Answers1

1

Try below code.

Dim data As Worksheet
Dim rng As Range

Set data = ThisWorkbook.Sheets("Sheet1")
Set rng = data.Range("A2")

Do While rng <> ""

ThisWorkbook.Worksheets.Add
ActiveSheet.Name = rng.Value
Set rng = rng.Offset(1, 0)
Loop

I assumed that your data starts from 2nd row in Sheet1 and you want the sheet name as per values in Column A.

If you want row number as sheet name for newly added sheet just use rng.row while assigning name to sheet.

Aditya Pansare
  • 1,112
  • 9
  • 14