Creating 64 new wsheets and naming them in vba

[mariasa]

Hi guys,

I have a list of 66 fund numbers and would like excel to create the
number of new worksheets within my workbook equal to the number of
funds (66) minus 2 (that is 64) since 3 already exist and I am using
the 1st one for the general info.

Then I would like excel to name the new worksheets appropriately
looping through a range of fund numbers (they are not consecutive
numbers so excel has to loop through them on my general info page)

So this is the task.

Here is the list of funds on my sheet1

Fund Number
172
175
203
204
219
229
256
261
267
269
272
284
285
292
293
309
332
338
351
561
581
582
601
614
616
620
622
624
632
636
637
640
662
684
687
688
693
706
709
713
801
806
818
824
828
830
831
839
848
860
863
864
865
869
870
871
872
891
931
939
941
942
943
947
949
953

and this is what I have so far in my vba:every time i run it, it
creates just one extra sheet and gives me a subscript out of range
error message.

Sub CommandButton1_Click()

Dim counter As Integer

For counter = 3 To 66
Sheets.Add
Sheets("counter").Select
Sheets("counter").Name = "counter"
Next counter
End Sub

Thank you, guys!!!

Best,
Maria

 

[OverAC]

Sub CommandButton1_Click()

Dim counter As Integer

For counter = 3 To 66
Sheets.Add
*Activesheet.Name = counter *<== change
Next counter
End Sub

Hope that help

OverAC

 

[Norman Jones]

Hi Mariasa,

Try:

'=============>>
Sub CommandButton1_Click()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = ThisWorkbook
Set SH = WB.Sheets("Sheet1")
Set rng = SH.Range("A4:A67") '<<==== CHANGE

On Error GoTo XIT
Application.ScreenUpdating = False

For Each rCell In rng.Cells
With rCell
Sheets.Add after:=WB.Sheets(WB.Sheets.Count)
ActiveSheet.Name = .Value
End With
Next rCell

XIT:

Application.ScreenUpdating = True

End Sub
'<<=============

 

[mariasa]

omg that worked brilliantly!!!! Thanks so much OverAC

Regards,
Mari

 

[mariasa]

Hi Norman,

Thanks for ur post, that works perfectly as well. Now i have two valid
methods

Sincerely,
Maria

 

[mariasa]

Guys,
how do I undo the sub? I mean if i wanted to modify code and include
other commands withing the For Next Procedure, but dont want to create
another 66 new sheets. So id like to undo it and repeat the procedure
with my revised code.

Thanks so much!!!!

Regards,
Maria

 

[Norman Jones]

Hi Mariasa,

See response to your subsequent post