Reset my object variable myRng1 with each loop
 

I'm setting in my FIRST Worksheet an object variable myRng1 by using code line:
Set myRng1 = Application.InputBox("Highlight the range you wish to Extract",
Type:=8)
This extract range ($P$80:$P$84) will remain the same for all successive
worksheet I loop through. I just need my myRng1 to "reflect" the current
sheet name as it loops, versus the original sheet name. See <PROBLEM Line
Below

my loop does as follows:
For i = StartSheetIdxNum To NumSheetsToEnd
Sheets(i).Activate
If i StartSheetIdxNum Then
Application.Goto Reference:=Range(mcell), Scroll:=True
Set myRng1 = Range(ActiveSheet.Name & "!" & Range(myRng1)) ' <PROBLEM
End If
ReDim MySArr(1 To numCells + 1, 1 To NumSheetsToEnd)
MySArr(1, c) = ActiveSheet.Name
For r = 2 To numCells + 1
MySArr(r, c) = myRng1(r - 1).Value
Next r
Call GoHome

Thanks in Advance,,

Reset my object variable myRng1 with each loop
 

For i = StartSheetIdxNum To NumSheetsToEnd
Sheets(i).Activate
If i StartSheetIdxNum Then
Application.Goto Reference:=Range(mcell), Scroll:=True
Set myRng1 =Activesheet.Range(myRng1.Address) ' <PROBLEM
End If
ReDim MySArr(1 To numCells + 1, 1 To NumSheetsToEnd)
MySArr(1, c) = ActiveSheet.Name
For r = 2 To numCells + 1
MySArr(r, c) = myRng1(r - 1).Value
Next r
Call GoHome

--
Regards,
Tom Ogilvy


"JMay" wrote:

I'm setting in my FIRST Worksheet an object variable myRng1 by using code line:
Set myRng1 = Application.InputBox("Highlight the range you wish to Extract",
Type:=8)
This extract range ($P$80:$P$84) will remain the same for all successive
worksheet I loop through. I just need my myRng1 to "reflect" the current
sheet name as it loops, versus the original sheet name. See <PROBLEM Line
Below

my loop does as follows:
For i = StartSheetIdxNum To NumSheetsToEnd
Sheets(i).Activate
If i StartSheetIdxNum Then
Application.Goto Reference:=Range(mcell), Scroll:=True
Set myRng1 = Range(ActiveSheet.Name & "!" & Range(myRng1)) ' <PROBLEM
End If
ReDim MySArr(1 To numCells + 1, 1 To NumSheetsToEnd)
MySArr(1, c) = ActiveSheet.Name
For r = 2 To numCells + 1
MySArr(r, c) = myRng1(r - 1).Value
Next r
Call GoHome

Thanks in Advance,,

Reset my object variable myRng1 with each loop
 

Set myRng1 = Range(ActiveSheet.Name & "!" & myRng1.Address)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JMay" wrote in message
...
I'm setting in my FIRST Worksheet an object variable myRng1 by using code
line:
Set myRng1 = Application.InputBox("Highlight the range you wish to
Extract",
Type:=8)
This extract range ($P$80:$P$84) will remain the same for all successive
worksheet I loop through. I just need my myRng1 to "reflect" the current
sheet name as it loops, versus the original sheet name. See <PROBLEM
Line
Below

my loop does as follows:
For i = StartSheetIdxNum To NumSheetsToEnd
Sheets(i).Activate
If i StartSheetIdxNum Then
Application.Goto Reference:=Range(mcell), Scroll:=True
Set myRng1 = Range(ActiveSheet.Name & "!" & Range(myRng1)) '
<PROBLEM
End If
ReDim MySArr(1 To numCells + 1, 1 To NumSheetsToEnd)
MySArr(1, c) = ActiveSheet.Name
For r = 2 To numCells + 1
MySArr(r, c) = myRng1(r - 1).Value
Next r
Call GoHome

Thanks in Advance,,

Reset my object variable myRng1 with each loop
 

Thanks BOTH Tom and Bob;;;


"Bob Phillips" wrote:

Set myRng1 = Range(ActiveSheet.Name & "!" & myRng1.Address)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JMay" wrote in message
...
I'm setting in my FIRST Worksheet an object variable myRng1 by using code
line:
Set myRng1 = Application.InputBox("Highlight the range you wish to
Extract",
Type:=8)
This extract range ($P$80:$P$84) will remain the same for all successive
worksheet I loop through. I just need my myRng1 to "reflect" the current
sheet name as it loops, versus the original sheet name. See <PROBLEM
Line
Below

my loop does as follows:
For i = StartSheetIdxNum To NumSheetsToEnd
Sheets(i).Activate
If i StartSheetIdxNum Then
Application.Goto Reference:=Range(mcell), Scroll:=True
Set myRng1 = Range(ActiveSheet.Name & "!" & Range(myRng1)) '
<PROBLEM
End If
ReDim MySArr(1 To numCells + 1, 1 To NumSheetsToEnd)
MySArr(1, c) = ActiveSheet.Name
For r = 2 To numCells + 1
MySArr(r, c) = myRng1(r - 1).Value
Next r
Call GoHome

Thanks in Advance,,