Macro Error when I leave sheet
 

I have a macro that is set-up on a timer to repeat every 15 secs. When I
click on a different sheet in the workbook I get:
Run-time error '1004' Select method of range class failed.
Is there a way that I can still work in other worksheets as this macro is
running?
Thanks,
Sub MarketDelta1()
'''''''''''''''''

Worksheets("Futures").Range("O2:AA2").Select
Selection.Copy
Worksheets("Futures").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Worksheets("Futures").Range("O2").Select
'''''''''''''''''
' Call StartTimer to schedule the procedure again
If Time TimeSerial(16, 0, 0) Then
'do nothing
Else
StartTimer
End If
End Sub

Macro Error when I leave sheet
 

Andy,

The problem is with the line

Worksheets("Futures").Range("O2:AA2").Select

You can't Select a range on a worksheet that is not already active -- you
can only Select on the ActiveSheet. Use two Selects:

Worksheets("Futures").Select
Range("O2:AA2").Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"andy" wrote in message
...
I have a macro that is set-up on a timer to repeat every 15 secs. When I
click on a different sheet in the workbook I get:
Run-time error '1004' Select method of range class failed.
Is there a way that I can still work in other worksheets as this macro is
running?
Thanks,
Sub MarketDelta1()
'''''''''''''''''

Worksheets("Futures").Range("O2:AA2").Select
Selection.Copy
Worksheets("Futures").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Worksheets("Futures").Range("O2").Select
'''''''''''''''''
' Call StartTimer to schedule the procedure again
If Time TimeSerial(16, 0, 0) Then
'do nothing
Else
StartTimer
End If
End Sub

Macro Error when I leave sheet
 

you can try this. you can select on any sheet but the active sheet. but you
don't need to select to copy and paste

Sub MarketDelta1()
'''''''''''''''''
Worksheets("Futures").Range("O2:AA2").Copy
Worksheets("Futures").Range("A2").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
' Worksheets("Futures").Range("O2").Select
'''''''''''''''''
' Call StartTimer to schedule the procedure again
If Time TimeSerial(16, 0, 0) Then
'do nothing
Else
StartTimer
End If
End Sub

--


Gary


"andy" wrote in message
...
I have a macro that is set-up on a timer to repeat every 15 secs. When I
click on a different sheet in the workbook I get:
Run-time error '1004' Select method of range class failed.
Is there a way that I can still work in other worksheets as this macro is
running?
Thanks,
Sub MarketDelta1()
'''''''''''''''''

Worksheets("Futures").Range("O2:AA2").Select
Selection.Copy
Worksheets("Futures").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Worksheets("Futures").Range("O2").Select
'''''''''''''''''
' Call StartTimer to schedule the procedure again
If Time TimeSerial(16, 0, 0) Then
'do nothing
Else
StartTimer
End If
End Sub