Can’t set range from two worksheets VBA

Question

I can’t understand why I keep getting the “Application-defined or object-defined error.” I have tried moving the code to a separate module and I’ve double checked worksheet names.

Sub CreateCalculations()
Set SummTrans = Worksheets(“Summary”).Range(“I3”, Range(“I3”).End(xlDown))
Set RegTrans = Worksheets(“Register”).Range(“A3”, Range(“A3”).End(xlDown))
End Sub

For some reason this code does work but it is not what I need.

Sub CreateCalculations()
Set SummTrans = Worksheets(“Summary”).Range(“I3”, Range(“I3”).End(xlDown))
Set RegTrans = Worksheets(“Register”).Range(“A3”)
End Sub

Answer 1

Try,

Sub CreateCalculations()
    Dim SummTrans As Range
    Dim RegTrans As Range
    
    With Worksheets("Summary")
        Set SummTrans = .Range("I3", .Range("I3").End(xlDown))
    End With
    With Worksheets("Register")
        Set RegTrans = .Range("A3", .Range("A3").End(xlDown))
    End With
End Sub