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

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

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