Object Required Error in VBA code

[davec8723]

Hello,

I am having some trouble with code I am trying to write. I need the variable LR to be redefined on each worksheet the code works on. I keep getting an error on the line underlined below. Hoping someone can help.

Sub Update_Actuals()


Dim LR As Variant, i As Long, NUM As Long, ws As Variant
NUM = Sheet2.Range("E46").Value


Application.ScreenUpdating = False


For Each ws In Array("MT IMP", "MT RES IMP", "MT R")


    With Sheets(ws)
[U]        Set LR = ws.Range("T" & ws.Rows.Count).End(xlUp).Row[/U]
        For i = 18 To LR
            .Range("H" & i).Copy
            .Range("H" & i).Resize(, NUM).PasteSpecial _
            Paste:=xlPasteFormulas


            i = i + 16
        Next i
    End With
    
Next
 
Application.CutCopyMode = False
Application.ScreenUpdating = True


MsgBox ("Done")
    
End Sub

 

[Andrew Poulsom]

You don't need ws. in the line of code you posted.

 

[davec8723]

Thank you for your response! If I remove the ws then I am getting a "Type Mismatch" error. Any idea why?

 

[Andrew Poulsom]

What's your code now and which line causes the error? LR should really be declared as Long rather than Variant.

 

[davec8723]

Here is the code. If I change LR to Long as soon as I click F8 to begin working through the code it returns an error that object is required. Thank you very much for your help.

The line "Set LR = .Range("T" & .Rows.Count).End(xlUp).Row" causes the error

Sub Update_Actuals()


Dim LR As Variant, i As Long, NUM As Long, ws As Variant
NUM = Sheet2.Range("E46").Value


'Application.ScreenUpdating = False


For Each ws In Array("MT IMP", "MT RES IMP", "MT R")


    With Sheets(ws)
        Set LR = .Range("T" & .Rows.Count).End(xlUp).Row
        For i = 18 To LR
            .Range("H" & i).Copy
            .Range("H" & i).Resize(, NUM).PasteSpecial _
            Paste:=xlPasteFormulas


            i = i + 16
        Next i
    End With
    
Next
 
Application.CutCopyMode = False
'Application.ScreenUpdating = True


MsgBox ("Done")
    
End Sub

 

[Andrew Poulsom]

You need to remove Set in:

Set LR = .Range("T" & .Rows.Count).End(xlUp).Row

The Set keyword is for object variables. Sorry I didn't spot that earlier.

 

[davec8723]

Thank you Andrew, that worked! I appreciate your time.