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

vba worksheet
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

Related questions

How to add a named sheet at the end of all Excel sheets?

I am trying to add an Excel sheet named "Temp" at the end of all existing sheets, but this code is not working: Private Sub CreateSheet() Dim ws As Worksheet ws.Name = "Tempo" Set ws = Sheets.Add(After:=Sheets(Sheets.…

Read More
Excel VBA For Each Worksheet Loop

I am working on code to basically go through each sheet in my Workbook, and then update column widths. Below is the code I wrote; I don't receive any errors, but it also doesn't actually do anything. Any help is …

Read More
Is there a macro to conditionally copy rows to another worksheet?

Is there a macro or a way to conditionally copy rows from one worksheet to another in Excel 2003? I'm pulling a list of data from SharePoint via a web query into a blank worksheet in Excel, and then I want …

Read More