Worksheets Names array with For Each

[ilikered100]

I'm sure I am missing something very simple, but I cannot get this code to work properly.

For the purpose of this example, let's say I have a workbook with 10 worksheets, each worksheet named with a different color. I want to use an array of three worksheet names ("Red", "Blue" , "Green") and clear the contents of only those three sheets. When I run the code below, whatever worksheet I'm on (whether in the array or not), the code runs three times on that sheet only. It only clears the content on the worksheet I am currently on.... again whether that worksheet is one of the three in the array or not. I've tried many variations of the below without success.

Sub ClearSpecificSheets()​

​

Dim ws As Worksheet​

Dim arrListofSheets As Variant​

​

Set arrListofSheets = ActiveWorkbook.Sheets(Array("Red", "Blue", "Green"))​

​

For Each ws In arrListofSheets​

Cells.Select​

Selection.ClearContents​

​

Next ws​

​

​

End Sub
​

Any help is appreciated.

Carolyn

 

[steve the fish]

Its because you arent qualifying the Cells with a sheet so by default excel will use the activesheet. Try:

For Each ws In arrListofSheets
    ws.Cells.ClearContents
Next ws

 

[ilikered100]

Thank you - It worked! I thought I had tried that. I would tell you how long I spent trying to get that this code to work, but I'd probably be kicked out of the Forum!

Thank you again!

Uggghhh!

Carolyn