Runtime Error 1004 Select method of Range class Failed

I used the macro recorder to generate this code which worked without
errors:
Sheets("Larry").Select
Columns("D:D").Select
Selection.ClearContents
Sheets("Harry").Select
Columns("D:D").Select
Selection.ClearContents
Range("C1").Select

So I generalized it to be this:
For Each ws In ThisWorkbook.Worksheets
strWsName = ws.Name
Worksheets(strWsName).Select
Worksheets(strWsName).Activate
Columns("D:D").Select
Selection.ClearContents
Next ws

But on the 2nd worksheet the Columns("D:D").Select gives me the
error in the Subject of this message.

Sugestions please! How do get Excel to run this without complaining?

BTW: Column D in this context is a message area.

Hi IanO,

It is rarely necessary or desirable to make physical selections. The
following code worked for me:

Sub Tester()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Columns("D:D").ClearContents
Next ws
End Sub


---
Regards,
Norman



"IanO" <[email protected]> wrote in message
news:[email protected]...
>I used the macro recorder to generate this code which worked without
> errors:
> Sheets("Larry").Select
> Columns("D:D").Select
> Selection.ClearContents
> Sheets("Harry").Select
> Columns("D:D").Select
> Selection.ClearContents
> Range("C1").Select
>
> So I generalized it to be this:
> For Each ws In ThisWorkbook.Worksheets
> strWsName = ws.Name
> Worksheets(strWsName).Select
> Worksheets(strWsName).Activate
> Columns("D:D").Select
> Selection.ClearContents
> Next ws
>
> But on the 2nd worksheet the Columns("D:D").Select gives me the
> error in the Subject of this message.
>
> Sugestions please! How do get Excel to run this without complaining?
>
> BTW: Column D in this context is a message area.
>

Thanks Norman.
IanO