Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Require solution for Run-time error 438

0

Hi,

I am facing "Run-time error 438" Object doesn't support this property or method for below coding in excel:

Option Explicit

Public interval As Date

Sub macro_timer()

interval = Now + TimeValue("00:00:10")

Application.OnTime interval, "MyATR"

End Sub

Sub MyATR()

' MyATR

' Keyboard Shortcut: Ctrl+Shift+R

Dim LstRw As Long, RepLst As Long 'varialble for last in Sheet 1 and Report

'Delete 163 rows after row 1 in Report first:

Application.ScreenUpdating = False

With Workbooks("Myexcel Testing.xlsm")

        .Worksheets("Report").Range("A2:A164").EntireRow.Delete

        RepLst = WorksheetFunction.Max(.Range("A" & .Rows.Count).End(xlUp).Row, 6) 'pick row 3 or more

' copy from Sheet 1

'With Workbooks("Myexcel Testing.xlsm")

        .Worksheets("Sheet1").Range("F2:K164").Copy

' paste to same rows in Report

        .Worksheets("Report").Range("A" & (RepLst + 1) & ":F" & (RepLst + 163)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Application.CutCopyMode = False

    ThisWorkbook.Save

End With

Application.ScreenUpdating = True

'MsgBox "Data added to Report"

'ActiveWorkbook.Save

Call macro_timer

End Sub

Sub stop_macro()

On Error Resume Next

Application.OnTime earliesttime:=interval, procedure:="MyATR", schedule:=False

End Sub

Please help for solution.

Regards,

Answer
Discuss

Discussion

Sunil

On which line do you get the error? The line in bold?

Please attach your file. E.g. I can't see why you have 6 as the min for the Worksheetfunction.Max line
John_Ru (rep: 6092) Jul 18, '21 at 6:08 am
Add to Discussion

Answers

0
Selected Answer

Sunil

Just spotted your problem. The first With line is for your workbook (so the .Range part of the bold line won't work since that requires a worksheet not a workbook).

Suggest you change this portion to (changes in bold):

With Workbooks("Myexcel Testing.xlsm").Worksheets("Report")

        .Range("A2:A164").EntireRow.Delete

        RepLst = WorksheetFunction.Max(.Range("A" & .Rows.Count).End(xlUp).Row, 6) 'pick row 3 or more

' copy from Sheet 1

Workbooks("Myexcel Testing.xlsm").Worksheets("Sheet1").Range("F2:K164").Copy

' paste to same rows in Report

        .Range("A" & (RepLst + 1) & ":F" & (RepLst + 163)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Application.CutCopyMode = False

    ThisWorkbook.Save

End With
Hope this works for you
Discuss

Discussion

Thank you very much John for your solution. You are a Genius of Excel. Teachexcel Team Rocks once again.
Regards,
SunilA (rep: 56) Jul 18, '21 at 8:49 am
BTW on the PasteSpecial line, you should be able to use  .Range("A" & (RepLst + 1)) alone, without the F part (as per the revision to my answer on your previous question). This assumes there's nothing in those columns beyond that cell (which is after the last cell in A!)- it's like when you manually paste a range into a single cell.
John_Ru (rep: 6092) Jul 18, '21 at 9:31 am
Thanks John for your inputs.
SunilA (rep: 56) Jul 21, '21 at 6:31 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login