CODEWITHSUNDEEP
excelvba
Gabriel Sabiá
Gabriel Sabiá

Reputation: 43

VBA - Get the name of all ActiveSheets

The following code returns the name of all the worksheets from the workbook. What I would like it to do is to return only the name of my active sheets.

I have multiple Sheets selected

What do I need to change to correct it? I suppose it's in that "For each" section

Sub test()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim x As Integer
    
    x = 0
    
    Dim aSheetnames As Variant
    aSheetnames = Array("")
    
    For Each ws In Worksheets
        'Redimensiona array
        ReDim Preserve aSheetnames(x)
        aSheetnames(x) = ws.Name
        x = x + 1
    Next ws
    
    Dim str As String
    
    For j = LBound(aSheetnames) To UBound(aSheetnames)
        str = str & aSheetnames(j) & Chr(13)
    Next j
    
    MsgBox str
End Sub

Upvotes: 0

Views: 247

Answers (1)

Holger
Holger

Reputation: 330

You can use the following code snippet to get all selected sheets.

ActiveWorkbook.Windows(1).SelectedSheets

Description: SelectedSheets

Otherwise you can also get the name of the activated sheet with

ThisWorkbook.ActiveSheet.Name

Description: ActiveSheet

In your case you have to change the For loop as follows:

For Each ws In ActiveWorkbook.Windows(1).SelectedSheets
    ReDim Preserve aSheetnames(x)
    aSheetnames(x) = ws.Name
    x = x + 1
Next ws

Upvotes: 2

Related Questions