Reset my object variable myRng1 with each loop

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,,
 
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
 
Set myRng1 = Range(ActiveSheet.Name & "!" & myRng1.Address)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks BOTH Tom and Bob;;;


Bob Phillips said:
Set myRng1 = Range(ActiveSheet.Name & "!" & myRng1.Address)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
Back
Top