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

Copy paste every interval

0

Hi,

When the macros is in running process and whenever i switch to new workbook. 

I am facing below error of:

Microsoft Visual basic 

Run-time error '1004':

Select method of worksheet class failed.

Attached excel sheet for your reference.

Please advise for solution, how to limit the macro for specific workbook.

Thanks,

Nitil

Answer
Discuss

Answers

0
Selected Answer

Nitil

Not sure what your copying is meant to acheive (at present) but your code uses statements like Columns ("A:C").Select which will apply to whatever workbook is active when the Windows timer completes (and may lead to problems, like you've found).

Better not to select cellsor ranges but just copy the values (without selection) and be specific which workbook it applies to. In the revised file and code below, I've done that using a With / End With statement:

Sub Action()
'
' Action Macro
' Copy paste every interval
'
' Keyboard Shortcut: Ctrl+Shift+A
'
With Workbooks("Copy paste every interval.xlsm")
        .Worksheets("Sheet1").Columns("A:C").Copy
        .Worksheets("Sheet2").Columns("A:C").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            'ActiveSheet.Previous.Select '(NOT NEEDED)
            Application.CutCopyMode = False
            .Worksheets("Sheet1").Range("E2") = Now

    End With
    Call macro_timer

End Sub
. Note that I've pasted a time in Sheet1 cell E2 so you can see something's happened when you're working in other files but note that the timer will cause a slight glitch (flash) even in the other file you are working on.

Also, your stop code referred to another procedure "LPT" so I've changed it to:

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


Answer the Question

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