asked on

retrieve the names of currently worksheets and enter these names into column A of worksheet, named 'Results'

Dear Experts:

I would like to run a macro that performs the following actions:

The macro is to retrieve the worksheet names of the currently selected worksheets and enter them into Column A (starting from A3) of the worksheet named 'Results'

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas

Subodh Tiwari (Neeraj)

Not quite sure I understand your requirement.

BTW give this a try and see if this is what you are trying to achieve.

Sub GetWorkSheetNames()
Dim ws As Worksheet
Dim r As Long
Sheet1.Range("A3").Value = "Results"
r = 3
For Each ws In ActiveWorkbook.Worksheets
    r = r + 1
    Sheet1.Range("A" & r).Value = ws.Name
Next ws
End Sub

Open in new window

Saqib Husain

Here is mine

Sub a()
    Set wsl = ThisWorkbook.Windows(1).SelectedSheets
    For i = 1 To wsl.Count
        Sheets("Results").Cells(2 + i, 1).Value = wsl(i).Name
    Next i
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreas Hermle

ASKER

Hi Neeraj, thank you very much, you were close, I was asking for selected sheets only. Thank you very much anyway

Andreas Hermle

ASKER

I was hoping to award points to the two of you, since both codes work fine and I due to the different approaches, I can learn from both. But awarding points for assisted solutions is regrettably not available anymore.

Since Saqib was quicker to answer, I clicked his answer ' best solution '.

thank you very much to you both for your great support.

Regards, Andreas

Subodh Tiwari (Neeraj)

You're welcome Andreas! Glad your issue is resolved.

Actually I couldn't get your requirement correctly first time.