How to select all active sheets?

Question

Everything is good. The only error I am getting here is the Select. How can I select all active sheets except for specific sheets? I want the Macro to run to all active sheets.

I have the macro code below and please let me know what I can do. I am still an amateur to excel VBA, but I am doing my best to learn more, so I can do more.

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("A:XFD").Select
    Range("A3").Activate
    Sheets(Array("Sheet1", "1191", "41", "16", "1223")).Select
    Sheets("1191").Activate
    Columns("A:XFD").Select
    Range("A3").Activate
    Range(Selection, Selection.End(xlToRight)).Select
    Range("C15").Select
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets(Array("1191", "41", "16", "1223")).Select
    Sheets("1191").Activate
    ActiveWindow.SmallScroll Down:=-9
    Range("D4").Select
    Sheets(Array("1191", "41", "16", "1223")).Select
    Sheets("1191").Activate
    Range("C4").Select
    Rows("2:2").EntireRow.AutoFit
    Range("D3").Select
    Sheets(Array("41", "16", "1223")).Select
    Sheets("41").Activate
    Rows("4:4").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("A4").Select
    ActiveWindow.SmallScroll Down:=-15
    Sheets(Array("41", "16", "1223")).Select
    Sheets("16").Activate
    ActiveWindow.SmallScroll Down:=-15
    Range("D17").Select
    Sheets(Array("41", "16", "1223")).Select
    Sheets("1223").Activate
    ActiveWindow.SmallScroll Down:=-15
    Sheets(Array("1191", "41", "16", "1223")).Select
    Sheets("1191").Activate
    Rows("2:2").Select
    Selection.RowHeight = 34.5
    Range("B3").Select
    Rows("3:3").RowHeight = 60
    Rows("4:4").RowHeight = 106.5
    Rows("4:4").RowHeight = 126
    Rows("5:5").RowHeight = 113.25
    Columns("A:A").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ColumnWidth = 4.71
    Cells.EntireColumn.AutoFit
    Columns("C:C").Select
    Selection.EntireColumn.Hidden = True
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Range("D4").Select
    Sheets("1191").Select
    Range("D4").Select
    Cells.Replace What:="Unavailable", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    With Application.ReplaceFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 6053069
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Cells.Replace What:="", Replacement:="", LookAt:=xlWhole, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True
    Range("D4").Select
    ActiveWindow.SmallScroll Down:=-24
    Sheets(Array("1191", "41", "16", "1223")).Select
    Sheets("1191").Activate
    Range("B4:F4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("D3").Select
    Sheets("1191").Select
    Range("D4").Select
End Sub

Rather than fixing your selects, I would suggest [reading this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and try to avoid using `.Select` as much as possible. Nearly any action you want to accomplish can be achieved without using select, and this would be much preferable as using select can be disrupted by users on execution. — Plutian, Feb 14 '20 at 11:01
That is basically just a macro recording. I see that you are doing a lot of other stuff in your code, can you elaborate on your desired output? Do you need to select multiple sheets to print as pdf or why is it necessary ? — Daghan, Feb 14 '20 at 11:02
@Daghan several things needed. Format column sizes, row sizes, remove unavailable from all worksheets, add fill color to all worksheets — Jezun, Feb 14 '20 at 12:10
What does "remove unavailable from all worksheets" mean? Which ranges to be resized and based on what to choose the necessary color? When you said "Format column sizes", what do you mean? To only auto-fit according to the contained values? If not, what else does this mean? And do that for all columns and rows of your "selected" sheets? Anyhow, it can/should be done without selection... — FaneDuru, Feb 14 '20 at 12:25

score 0 · Answer 1 · answered Feb 14 '20 at 13:12

Except for the sheet name you want to exclude, put the sheet name in the array, and then select the sheet as the array.

Sub test()
    Dim Ws As Worksheet
    Dim vR() As Variant
    Dim n As Integer
    For Each Ws In Worksheets
        If Ws.Name <> "Except Sheet Name" Then
            n = n + 1
            ReDim Preserve vR(1 To n)
            vR(n) = Ws.Name
        End If
    Next Ws
    Sheets(vR).Select
End Sub

How to select all active sheets?

1 Answers1