Run-time error 438 when trying to set a range

[szita2000]

Hi Guys.

I can't seem to figure this one out.

I am attempting to copy from the workbook containing the macro to another workbook.
For the copy and paste I need to set a range in the target workbook that contains the day numbers on Sheet3 in a named range

It crops out with error "Object doesn't support this property or method" at the line:
Set TargetDays = TargetWorkbook.Sheet3.Range("rngMasterDaysHorizontal")

Wonder if this is because I am trying to set an object in another workbook?

Instead of the copy and paste method I am trying to set the values to the source values.

Sub openMaster()

    Dim TargetDays As Range
    Dim CorrectColumn As Integer
    Dim Shiftreport As Workbook
    Dim SourceDays As Range
    Dim SourceDAY As Integer        'This will be a column number
    Dim TargetWorkbook As Workbook
   
   
    'Opening the workbook
    Workbooks.Open (Sheet5.Range("rngMasterFilePath").Value)
   
    Set TargetWorkbook = ActiveWorkbook
   
    'Setting the searchrange
    Set TargetDays = TargetWorkbook.Sheet3.Range("rngMasterDaysHorizontal")
    'Finding the correct column
    CorrectColumn = TargetDays.Find(ThisDay).Column
   
    Set Shiftreport = ThisWorkbook
    Set SourceDays = Sheet8.Range("rngOutOutputDaysHorizontal")
    SourceDAY = SourceDays.Find(ThisDay).Column
   
        TargetWorkbook.Sheet3.Cells(11, CorrectColumn).Value = Shiftreport.Sheet8.Cells(20, SourceDAY).Value
        TargetWorkbook.Sheet3.Cells(13, CorrectColumn).Value = Shiftreport.Sheet8.Cells(22, SourceDAY).Value
        TargetWorkbook.Sheet3.Cells(14, CorrectColumn).Value = Shiftreport.Sheet8.Cells(23, SourceDAY).Value
        TargetWorkbook.Sheet3.Cells(15, CorrectColumn).Value = Shiftreport.Sheet8.Cells(24, SourceDAY).Value
End Sub

 

[RoryA]

You can't directly access a sheet in another workbook using its codename (unless you set a reference between the two and even then you'd access it as a member of the project, not the workbook). Did you mean:

Set TargetDays = TargetWorkbook.Sheets(Sheet3").Range("rngMasterDaysHorizontal")

 

[jkpieterse]

Alternatively, since you're using a range name:

Set TargetDays = TargetWorkbook.Names("rngMasterDaysHorizontal").RefersToRange

 

[szita2000]

Thanks guys, both of these worked!