Reset my object variable myRng1 with each loop

[JMay]

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,,

 

[Tom Ogilvy]

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

 

[Bob Phillips]

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]

Thanks BOTH Tom and Bob;;;

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


--
HTH

Bob

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