How can I use VBA to list all worksheets in a workbook within a string?

seegoon picture seegoon · May 18, 2012 · Viewed 57.4k times · Source

How can I use VBA to get the names of all the worksheets (or alternatively a maximum of three) within the active workbook to be returned as a string? For context, I will then use the string in naming the workbook when it is saved. I have figured out how to create a savename for the file using input dialogs and so on, so it's only a case of getting VBA to return something like "[Worksheet 1 name] and [Worksheet 2 name]".

Thanks so much!

Answer

Scott Holtzman picture Scott Holtzman · May 18, 2012
Option Explicit

Sub namesheets()

Dim wks as Worksheet, strName as String

For each wks in Worksheets
     strName = strName & wks.Name
Next

End Sub

You can also google "Excel vba loop through worksheets in a workbook" or something to that effect and find the answer very easily.

Also, this question was asked in some form on this website. See link... Loop through subset of worksheets