• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 33
  • Last Modified:

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
0
Andreas Hermle
Asked:
Andreas Hermle
  • 3
  • 2
  • 2
2 Solutions
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

0
 
Saqib Husain, SyedEngineerCommented:
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

0
 
Saqib Husain, SyedEngineerCommented:
In a more formal way
Sub ListSelectedSheets()
Dim wsl As Sheets
Dim i As Integer
    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

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Now I get what you meant....

Here is the tweaked code
Sub GetWorkSheetNames()
Dim ws As Worksheet, dws As Worksheet
Dim r As Long
Set dws = Sheets("Results")
r = 3
For Each ws In ActiveWindow.SelectedSheets
    If ws.Name <> dws.Name Then
        dws.Range("A" & r).Value = ws.Name
        r = r + 1
    End If
Next ws
End Sub

Open in new window

0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Neeraj, thank you very much, you were close, I was asking for selected sheets only. Thank you very much anyway
0
 
Andreas HermleTeam leaderAuthor Commented:
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
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Andreas! Glad your issue is resolved.

Actually I couldn't get your requirement correctly first time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now