This is really perplexing me! I have a very, very simple action to perform
(select a worksheet, select column A, clear contents, return to different
sheet).

If I record a Macro it works fine. If I take the code from the macro and
use it as part of a longer sub, it falls over.

Code:

Sub ClearA()

Sheets("Statistics").Select
Columns("A:A").Select ' // falls over here
Selection.ClearContents
Sheets("NameList").Select

End Sub

It falls over with the error message :

Runtime Error '1004'
Select method of Range class failed


I must be missing something obvious but I'm darned if I can see why it works
as a Macro and not in a sub. Can anyone help please?

This shouldn't have held me up for the last two hours but it has!!

Thanks in advance
Sue

try this and see if it works for you

Sub ClearA()

Sheets("Statistics").Columns("A:A").ClearContents
Sheets("NameList").Select

End Sub

--


Gary


"SueJB" wrote in message
...
This is really perplexing me! I have a very, very simple action to
perform
(select a worksheet, select column A, clear contents, return to different
sheet).

If I record a Macro it works fine. If I take the code from the macro and
use it as part of a longer sub, it falls over.

Code:

Sub ClearA()

Sheets("Statistics").Select
Columns("A:A").Select ' // falls over here
Selection.ClearContents
Sheets("NameList").Select

End Sub

It falls over with the error message :

Runtime Error '1004'
Select method of Range class failed


I must be missing something obvious but I'm darned if I can see why it
works
as a Macro and not in a sub. Can anyone help please?

This shouldn't have held me up for the last two hours but it has!!

Thanks in advance
Sue

Hi Sue,

Try:

Sub ClearA()

Sheets("Statistics").Columns("A:A").ClearContents
Sheets("NameList").Select

End Sub

---
Regards,
Norman



"SueJB" wrote in message
...
This is really perplexing me! I have a very, very simple action to
perform
(select a worksheet, select column A, clear contents, return to different
sheet).

If I record a Macro it works fine. If I take the code from the macro and
use it as part of a longer sub, it falls over.

Code:

Sub ClearA()

Sheets("Statistics").Select
Columns("A:A").Select ' // falls over here
Selection.ClearContents
Sheets("NameList").Select

End Sub

It falls over with the error message :

Runtime Error '1004'
Select method of Range class failed


I must be missing something obvious but I'm darned if I can see why it
works
as a Macro and not in a sub. Can anyone help please?

This shouldn't have held me up for the last two hours but it has!!

Thanks in advance
Sue

Hi Gary

Yes, it worked ... just a case of wrong syntax, I suppose?

Many thanks for such a quick and helpful reply

SueJB

"Gary Keramidas" wrote:

try this and see if it works for you

Sub ClearA()

Sheets("Statistics").Columns("A:A").ClearContents
Sheets("NameList").Select

End Sub

--


Gary


"SueJB" wrote in message
...
This is really perplexing me! I have a very, very simple action to
perform
(select a worksheet, select column A, clear contents, return to different
sheet).

If I record a Macro it works fine. If I take the code from the macro and
use it as part of a longer sub, it falls over.

Code:

Sub ClearA()

Sheets("Statistics").Select
Columns("A:A").Select ' // falls over here
Selection.ClearContents
Sheets("NameList").Select

End Sub

It falls over with the error message :

Runtime Error '1004'
Select method of Range class failed


I must be missing something obvious but I'm darned if I can see why it
works
as a Macro and not in a sub. Can anyone help please?

This shouldn't have held me up for the last two hours but it has!!

Thanks in advance
Sue

Many thanks Norman

SJB

"Norman Jones" wrote:

Hi Sue,

Try:

Sub ClearA()

Sheets("Statistics").Columns("A:A").ClearContents
Sheets("NameList").Select

End Sub

---
Regards,
Norman



"SueJB" wrote in message
...
This is really perplexing me! I have a very, very simple action to
perform
(select a worksheet, select column A, clear contents, return to different
sheet).

If I record a Macro it works fine. If I take the code from the macro and
use it as part of a longer sub, it falls over.

Code:

Sub ClearA()

Sheets("Statistics").Select
Columns("A:A").Select ' // falls over here
Selection.ClearContents
Sheets("NameList").Select

End Sub

It falls over with the error message :

Runtime Error '1004'
Select method of Range class failed


I must be missing something obvious but I'm darned if I can see why it
works
as a Macro and not in a sub. Can anyone help please?

This shouldn't have held me up for the last two hours but it has!!

Thanks in advance
Sue