Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating 64 new wsheets and naming them in vba
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 -- mariasa ------------------------------------------------------------------------ mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726 View this thread: http://www.excelforum.com/showthread...hreadid=521661 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating 64 new wsheets and naming them in vba
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 -- OverAC ------------------------------------------------------------------------ OverAC's Profile: http://www.excelforum.com/member.php...o&userid=32396 View this thread: http://www.excelforum.com/showthread...hreadid=521661 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating 64 new wsheets and naming them in vba
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 '<<============= --- Regards, Norman "mariasa" wrote in message ... 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 -- mariasa ------------------------------------------------------------------------ mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726 View this thread: http://www.excelforum.com/showthread...hreadid=521661 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating 64 new wsheets and naming them in vba
omg that worked brilliantly!!!! Thanks so much OverAC :-) Regards, Mari -- marias ----------------------------------------------------------------------- mariasa's Profile: http://www.excelforum.com/member.php...fo&userid=3172 View this thread: http://www.excelforum.com/showthread.php?threadid=52166 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating 64 new wsheets and naming them in vba
Hi Norman, Thanks for ur post, that works perfectly as well. Now i have two valid methods :-) Sincerely, Maria -- mariasa ------------------------------------------------------------------------ mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726 View this thread: http://www.excelforum.com/showthread...hreadid=521661 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating 64 new wsheets and naming them in vba
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 -- mariasa ------------------------------------------------------------------------ mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726 View this thread: http://www.excelforum.com/showthread...hreadid=521661 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating 64 new wsheets and naming them in vba
Hi Mariasa,
See response to your subsequent post --- Regards, Norman "mariasa" wrote in message ... 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 -- mariasa ------------------------------------------------------------------------ mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726 View this thread: http://www.excelforum.com/showthread...hreadid=521661 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you set up a global footer (filename & ext) on all wsheets | Setting up and Configuration of Excel | |||
Creating/Naming New Worksheets Based on Select Cells in Master Worksheet | Excel Worksheet Functions | |||
Data from 3 wsheets into billing invoice wsheet | Excel Worksheet Functions | |||
Creating and naming new worksheets | Excel Programming | |||
Creating & Naming a New Worksheet | Excel Programming |