4

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!

seegoon
  • 563
  • 1
  • 8
  • 15

1 Answers1

16
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

Community
  • 1
  • 1
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Thanks for this. I do have a couple of followup questions - thanks to my poor knowledge of VBA, I must admit. Initially, how would I use this string? I currently have code which saves a file using strings: SaveFile = VendorCode & " - " & VendorName & " - " & ContentDate - what would I insert in there to utilise this string? Lastly, would there be a way of inserting commas between the worksheet names? Thank you so much! – seegoon May 21 '12 at 09:31
  • I would ask that you do a little diving into what you have. You are trying to build a string, and you have a string built out of variables and text (" - "). Continue on the basis of what I wrote and what you are already have and you will be able to make your string (Hint = a = Scott b = Holtzman -> strName = a & " " & b strName = Scott Holtzman. Please don't forget to mark your answer as accepted if you feel you got what you asked for. – Scott Holtzman May 21 '12 at 14:08