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: 1917) 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: 34) 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: 1917) Jul 18, '21 at 9:31 am
Thanks John for your inputs.
SunilA (rep: 34) 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