Excel 2000 SP3 (if it matters).
I need vba code to retrieve the text data from cells in a DIFFERENT sheet than the one containing the controls that fires the macro.
I've been using the code below ... which works fine when the macro is fired from the same sheet as the range I'm referencing. But it won't work if the range is in a different sheet.
ActiveSheet.Shapes(rowindex).Select
Selection.Characters.Caption = Range("ServiceNames").Cells(rowindex, 5).FormulaR1C1
I've tried adding changing "ActiveSheet" to "Sheets("mysheetname")", and a couple of other things ... but without success. I need to access the data as a Range because I'm "stepping through" the range using offsets from R1C1 in the range.
And one other thing, while we're here ... is there any way for me to combine the two lines above into only one ... so that I don't actually select the Shape prior to changing the Caption.
Thanks in advance!
Adam
I need vba code to retrieve the text data from cells in a DIFFERENT sheet than the one containing the controls that fires the macro.
I've been using the code below ... which works fine when the macro is fired from the same sheet as the range I'm referencing. But it won't work if the range is in a different sheet.
ActiveSheet.Shapes(rowindex).Select
Selection.Characters.Caption = Range("ServiceNames").Cells(rowindex, 5).FormulaR1C1
I've tried adding changing "ActiveSheet" to "Sheets("mysheetname")", and a couple of other things ... but without success. I need to access the data as a Range because I'm "stepping through" the range using offsets from R1C1 in the range.
And one other thing, while we're here ... is there any way for me to combine the two lines above into only one ... so that I don't actually select the Shape prior to changing the Caption.
Thanks in advance!
Adam