Variable from different Workbook

[Partjob]

It seems I have worked on this bit of code for months and it was working until the sheet that the data is coming from was changed. To allow for this and any further possible changes I did the following.

Workbooks.Open Filename:=Fnm 'place file path of workbook in cell in column A
    With ActiveWorkbook
    [COLOR=red]Rnum = Sheet43.Range("S" & Rows.Count).End(xlUp).Row
[/COLOR]    CopRan = Range(Range("S" & Rnum), Range("S" & Rnum).Offset(0, Prolen)).Address
    'find Range for copy
    Sheets("Outline Activity Schedule").Range(CopRan).Copy
       Workbooks(MyWbk).Sheets("Prediction").Cells(Target.Row, Pastecol).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False

So just to clarify what is going on here. An external workbook is being opened with the variable "Fnm" Once this is open I need to declare the row where the data will be, and pass it to the next line to set the next variable "CopRan" It is always in Column "S".
This all works if I don't try and do this ie drop the red line and have an actual number instead of the variable.
I have Dim'd the variables at the beginning of the code.
If I put this line in the immeadiate window on the target sheet I do get the result I require.
I get the Error "Object Required" Run time Error 424
Thanks Partjob

 

[onlyadrafter]

Hello,

my guess would be

Rnum = sheets("Sheet43").Range("S" & Rows.Count).End(xlUp).Row

 

[Norie]

My guess would be to create references to the workbooks.
For example:

Set wbThis = ThisWorkboook ' create reference to workbook the code is in

Set wbAct  = ActiveWorkbook ' create reference to the active workbook

Set wbOpen = Workbooks.Open("C:\MyPath\MyBook.xls") ' open workbook and create a reference to it

Set wbNew = Workbooks.Add ' create new workbook and a reference to it

 

[Partjob]

Thanks onlyadrafter
I had tried all sorts there but not Sheets.
However I now have a different error "Subscript out of range"
This is I think because it is still looking at the original workbook, this has not got a Sheet43, only the target book has a Sheet43. I think it is probably worth posting the whole code

Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim Fnm As String
Dim Myrow As Integer
Dim MyWbk As String
Dim Pastecol As Integer
Dim CopRan As String
Dim Dat As Long
Dim Prolen As Integer
Dim Strdt As Long
Dim Rnum As Integer
 If AbortChangeEvent = True Then Exit Sub
'Reference point for dates
Dat = Sheet2.Cells(2, 9).Value
'Length of project to create copy range variable
Prolen = Sheet1.Cells(Target.Row, 19).Value - 1
'Date project starts
Strdt = Sheet1.Cells(Target.Row, 18).Value
'varable to work out where to paste the data
Pastecol = 9 + DateDiff("m", Dat, Strdt)
'set copy range
MyWbk = ThisWorkbook.Name
Fnm = Sheet2.Cells(Target.Row, 1).Text
If Target.Column <> 1 Then Exit Sub
If Dir(Fnm) = "" Then MsgBox "You have made an error try again", vbOKOnly, "David Masters"
If Target.Column = 1 And Fnm = "" Then
MsgBox "You have made an error try again", vbOKOnly, "David Masters"
Application.ScreenUpdating = True
     Exit Sub
    Else
    Workbooks.Open Filename:=Fnm 'place file path of workbook in cell in column A
    With ActiveWorkbook
    [COLOR=red]Sheets("Sheet43").Range("S" & Rows.Count).End(xlUp).Row[/COLOR]
    CopRan = Range(Range("S" & Rnum), Range("S" & Rnum).Offset(0, Prolen)).Address
    'find Range for copy
    Sheets("Outline Activity Schedule").Range(CopRan).Copy
       Workbooks(MyWbk).Sheets("Prediction").Cells(Target.Row, Pastecol).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End With
    End If
ActiveWorkbook.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

I still have a problem with the red line with the error listed above.
thanks Partjob