Sheet Code not working with other workbooks

[earldunning]

I have code in a sheet (not module). I create the sheet in a new workbook on the fly (within a macro) but want the new sheet to have some functionality. Having code in the copy sheet was a way I could get macros into the new sheet.

The sheet code calls another workbook and opens to a tab. Then I need to search the newly opened workbook and place a value in it.
The problem is, the sheet code does not work beyond opening it and going to the tab. The code works fine in a module but I dont have that luxery at this time.

Any help would be appreciated. Thanks

(this code is inside a sheet - not a module)
Dim ListLength As Long
Dim cntr As Integer
Dim UniqueCRID As Integer
Dim UniqueIDFound As Boolean

Workbooks.Open Password:="release1", Filename:="\\ohlewnas0240.nwie.net\claims\Release Demand Info\Release And Demand Master.xls" 'works
Sheets("ChangeReq").Select 'works
ListLength = Range("A" & Rows.Count).End(xlUp).Row [COLOR="Red"]'doesnt work - picks up sheet info not other workbook[/COLOR]
MsgBox ("ListLength = " & ListLength)
MsgBox ("UniqueID = " & UniqueCRID)
ListLength = 165
cntr = 3
Do While cntr < ListLength + 1    [COLOR="Red"]'doesnt work - searches through sheet not other workbook [/COLOR]
    'MsgBox ("addedpoperly is " & AddedProperly & "row is " & cntr)
    If Range("A" & cntr) = UniqueCRID Then
        UniqueIDFound = True
        GoTo 10
    End If
    cntr = cntr + 1
Loop

 

[dave3009]

Hi

You need to tell the macro that it wants to look in the other book so

ActiveWorkbook.Range.....................

HTH


Dave

 

[earldunning]

Error:
Object does not support this property or method

When I changed it to this:
ListLength = ActiveWorkbook.Range("A" & Rows.Count).End(xlUp).Row

 

[dave3009]

Opps, sorry

Activeworkbook.Activesheet.Range

 

[earldunning]

ListLength = ActiveWorkbook.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Same error

I also tried:
Workbooks("Release and Demand Master").Activate
&
ListLength = Workbooks("Release and Demand Master").Sheets("ChangeReq").Range("A" & Rows.Count).End(xlUp).Row


Both errored with "Subscript our of range"

 

[earldunning]

Since the code worked in a regular module and not the sheet, could there be limitations on sheet code?

 

[earldunning]

Found a typo in that last bit. It may be working. I am messing with it now. I will respond in a bit.

 

[earldunning]

Its working! Thanks a a lot!