Results 1 to 2 of 2
2004-11-23, 21:16 #1
- Join Date
- Oct 2002
- Waltham, Massachusetts, USA
- Thanked 0 Times in 0 Posts
Getting cells in other worksheets (Excel / VBA 2003)
I have a workbook with 33 sheets. I want to show row 76 of each of sheets 3 through 33 on a "summary page" on sheet 2. I can get the name of the sheets via:
Public Function WSName(i As Integer) As String
WSName = Worksheets(i).Name
but I can't find an Excel worksheet function which will take this string and allow me to access cells on a worksheet given the name of the worksheet as a string.
So, I grabbed this from somewhere ...
Public Function WSContents(i As Integer, r)
WSContents = Worksheets(i).Range(r.Address).Value
which allows me to refer to cells on other worksheets as (e.g.) WSContents(5, D76), but I have to call this for every cell I want, which is a lot.
This seems like an awfully inefficient hammer to crack this particular nut: once I have a few hundred of these calls on my summary sheet, recalculating after any change takes a noticeable amount of time, perhaps a few seconds. I do need the volatile, right?
Is there a better approach??
p.s. As a bonus question, what's the type of the argument 'r'?
2004-11-23, 21:49 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Getting cells in other worksheets (Excel / VBA 2003)
Bonus question first: strictly speaking, r is a Variant since its type has not been declared. From the code, it becomes clear that the code expects a Range.
You can use the INDIRECT function to process a string that evaluates to a cell address: