worksheet array

[VegasNath]

Code: Select all

Dim ws As Variant
Dim arrws As Variant

    arrws = Array( _
        "Rpt 1", _
        "Rpt 2", _
        "Rpt 2a", _
        "Rpt 2b", _
        "Rpt 3", _
        "Rpt 3al", _
        "Rpt Consol")

    For Each ws In arrws
        Range("A4:AP100").ClearContents
        Range("A4").Select
    Next

Why am I not getting the expected results here? It seems to only work on whichever of the sheets is currently active? I tried using ws.select inside the loop without success.

[HansV]

You don't specify which sheet Range(...) belongs to, so Excel assumes you mean the active sheet.
Moreover, ws will NOT be a worksheet, but a string.

Try changing

Code: Select all

    For Each ws In arrws
        Range("A4:AP100").ClearContents
        Range("A4").Select
    Next

to

Code: Select all

    For Each ws In arrws
        With Worksheets(ws)
            .Range("A4:AP100").ClearContents
            .Range("A4").Select
        End With
    Next ws

Note the dot before the word Range.

[VegasNath]

Thanks Hans. I had to add a .select aswell before Range("A4").Select

[VegasNath]

my brain is fried.

Code: Select all

Dim ws As Worksheet, ws1 As Worksheet

Set ws1 = Sheets("5.2s Report")

    For Each ws In Worksheets
        If ws.Name <> ws1 And ws.Name <> "Holidays" Then ws.Delete
    Next

Object does not support property or method. Where am I going wrong?

[HansV]

ws.Name is a string, and ws1 is a worksheet object.

Change

If ws.Name <> ws1 And ws.Name <> "Holidays" Then ws.Delete

to

If ws.Name <> ws1.Name And ws.Name <> "Holidays" Then ws.Delete

If that doesn't work, you'll have to loop backwards.

[VegasNath]

Thanks.
Why do I miss the obvious?