Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
an array to step through sheets
I only write VBA once in a blue moon and so I am probably asking the obvious
I want to use a list of sheetnames (that will grow over time) held in the "companies" sheet ,cells b1 to b?? to pass control to a routine that selects each sheet in turn, carries out some code and then selects the next sheet... Obviously I can do this using a routine to find the row of the last name in column B and then a LOOP Sub WSheetSelectL( ' selects each sheet name down the list & activates that shee Sheets("companies").Selec Range("b1").Offset(1, 0).Selec Do While Not IsEmpty(ActiveCell Nrow = (ActiveCell.Row Sname$ = Cells(Nrow, 2 ' passes the sheet name to a routine that executes more code on the named shee Sheets("companies").Selec ActiveCell.Offset(1, 0).Selec Loo End Sub However, I am sure that I could use an array and be a lot neater/efficient. Trying this has brought me to the limit of my knowledge, I can populate the array from the cells, but can I use it? Can I h**l CAN ANYONE SUGGEST A WAY |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
an array to step through sheets
Hi David,
I would use the Dymamic Range: http://www.contextures.com/xlNames01.html#Dynamic Best regards Wolf -----Original Message----- I only write VBA once in a blue moon and so I am probably asking the obvious! I want to use a list of sheetnames (that will grow over time) held in the "companies" sheet ,cells b1 to b?? to pass control to a routine that selects each sheet in turn, carries out some code and then selects the next sheet.... Obviously I can do this using a routine to find the row of the last name in column B and then a LOOP: Sub WSheetSelectL() ' selects each sheet name down the list & activates that sheet Sheets("companies").Select Range("b1").Offset(1, 0).Select Do While Not IsEmpty(ActiveCell) Nrow = (ActiveCell.Row) Sname$ = Cells(Nrow, 2) ' passes the sheet name to a routine that executes more code on the named sheet Sheets("companies").Select ActiveCell.Offset(1, 0).Select Loop End Sub However, I am sure that I could use an array and be a lot neater/efficient. Trying this has brought me to the limit of my knowledge, I can populate the array from the cells, but can I use it? Can I h**l! CAN ANYONE SUGGEST A WAY? . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
an array to step through sheets
One way:
First, using Insert/Name/Define, define a workbook name (say Companies). If you want, you can make it dynamic so that it will automatically change as you add/delete companies: Name in Workbook: Companies Refers to: =Offset('Companies'!$A$1,,,COUNTA('Companies'!$A:$ A),1) then in your macro: Public Sub DoSheetArray() Dim vSheets As Variant Dim i As Long vSheets = ThisWorkbook.Names("Companies").RefersToRange.Valu e For i = 1 To UBound(vSheets, 1) With Worksheets(vSheets(i, 1)) 'do something .Range("A1") = vSheets(i, 1) End With Next i End Sub Assigning a range.Value to a variant in this way creates a two-dimensional 1-based array, equivalent to Redim vSheets(1 to <# of rows, 1 to <# of columns) Note that you don't need to actually select the sheets as long as you qualify the range references. In article , "David Shearer" wrote: I only write VBA once in a blue moon and so I am probably asking the obvious! I want to use a list of sheetnames (that will grow over time) held in the "companies" sheet ,cells b1 to b?? to pass control to a routine that selects each sheet in turn, carries out some code and then selects the next sheet.... Obviously I can do this using a routine to find the row of the last name in column B and then a LOOP: Sub WSheetSelectL() ' selects each sheet name down the list & activates that sheet Sheets("companies").Select Range("b1").Offset(1, 0).Select Do While Not IsEmpty(ActiveCell) Nrow = (ActiveCell.Row) Sname$ = Cells(Nrow, 2) ' passes the sheet name to a routine that executes more code on the named sheet Sheets("companies").Select ActiveCell.Offset(1, 0).Select Loop End Sub However, I am sure that I could use an array and be a lot neater/efficient. Trying this has brought me to the limit of my knowledge, I can populate the array from the cells, but can I use it? Can I h**l! CAN ANYONE SUGGEST A WAY? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
an array to step through sheets
David,
What advantage do you think an array will bring? If you intend to perform a similar action on every sheet, you will need to iterate through the sheet names and process each one, and this is true whether the names are on a worksheet or in an array. In fact, with an array you will need to transfer the names from the sheet to the array. Your code could be slightly improves like so, which removes all of the unnecessary selecting Sub WSheetSelectL() Dim i As Long Dim nRow As Long Dim Sname$ ' selects each sheet name down the list & activates that sheet i = 2 With Sheets("companies") Do While Not IsEmpty(.Cells(i, "B").Value) Sname$ = Cells(i, "B") ' passes the sheet name to a routine that executes more code on the named sheet i = i + 1 Loop End With End Sub You could also just loop through the sheet names without storing them, like so Dim sh As Worksheet For Each sh in Activeworkbook.Worksheets sname$ = sh.Name ' passes the sheet name to a routine that executes more code on the named sheet Next sh but this will process all sheets, but may not be what you want. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "David Shearer" wrote in message ... I only write VBA once in a blue moon and so I am probably asking the obvious! I want to use a list of sheetnames (that will grow over time) held in the "companies" sheet ,cells b1 to b?? to pass control to a routine that selects each sheet in turn, carries out some code and then selects the next sheet.... Obviously I can do this using a routine to find the row of the last name in column B and then a LOOP: Sub WSheetSelectL() ' selects each sheet name down the list & activates that sheet Sheets("companies").Select Range("b1").Offset(1, 0).Select Do While Not IsEmpty(ActiveCell) Nrow = (ActiveCell.Row) Sname$ = Cells(Nrow, 2) ' passes the sheet name to a routine that executes more code on the named sheet Sheets("companies").Select ActiveCell.Offset(1, 0).Select Loop End Sub However, I am sure that I could use an array and be a lot neater/efficient. Trying this has brought me to the limit of my knowledge, I can populate the array from the cells, but can I use it? Can I h**l! CAN ANYONE SUGGEST A WAY? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Drop Down List with Step by Step Instructions for 2007 | Excel Worksheet Functions | |||
Need step by step to add invoice numbering to excel template | New Users to Excel | |||
Can anyone povide step by step instructions on how to do the follo | Excel Discussion (Misc queries) | |||
What is the step-by-step procedure for making a data list? | Excel Discussion (Misc queries) | |||
I need step by step instructions to create a macro for 10 imbedde. | Excel Worksheet Functions |