I am writing a report based on a spreadsheet with a zillion sheets and associated tabs that are going to be related to a summary table in the report (MS Word). Do any of you know if there is a way of quickly and easily copying the tabs’ labels’ text and pasting them all into a Word document? For example assume five tabs labelled “Car”, “Coffee Table”, “Telephone”, “Remote”, and “Fan” and I want those five words in a Word doc. I don’t care how they appear as long as I can convert text to table.
Is this possible?
Thanks
I expect you’d need to write/record a macro to do that - it shouldn’t be too complex.
I’m not sure about copying from excel to word using a macro (and I’m not near a Windows PC where I can check), but I should imagine it’s possible.
The trick to being able to easily copy the tab names is to display them in the sheet itself. To do this, place this formula somewhere on the sheet:
=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255)
That will give you a cell with just the tab name, nice and ready for ctrl+C&V 
ETA: If you want to do the copying manually, then create a new tab and create a list of cells that point to the cells with the formulas above that you put on each tab. That’ll give you a nice list of tab names that you can then copy to your word doc.
If you’re looking for a way to create a dynamic Word doc that changes when you rename tabs in Excel, then Angry Badger has the convoluted (but correct) way to get the sheet name. Once you have that, you can paste it into Word as a link. Click Paste Special and select the Paste Link option. This is a lot of copy/pasting, so maybe not what you’re looking for.
If you are looking for a one-time way to get all the sheet names into Word as plain text, then a quick macro in Excel is the way to go. Loop through each worksheet in the workbook and output the name. The code below outputs it to the debug window.
For Each s In ActiveWorkbook.Worksheets
Debug.Print s.Name
Next
hghgfsdhgffgshadfgfkgshggyhsdfshhgfjgdjhggjyf
Start Microsoft Word, and then on the Mailings tab, in the Create goup, click Labels, in the address box type the text that you want. To change the formatting, select the text, right-click the selected text, and then click Font or Paragraph on the shortcut menu. To select the label type and other options, click Options. In the Label Options dialog box, make your choices, and then click OK.
This will make a list on a sheet in Excel named “Index” that you can copy & paste wherever.
Sub ListSheetNames()
Dim ws As Worksheet, i As Integer
Worksheets.Add(before:=Worksheets(1)).Name = “Index”
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> “Index” Then
i = i + 1
Sheets(“Index”).Range(“A” & i).Value = ws.Name
End If
Next ws
Sheets(“Index”).Columns(“A”).AutoFit
End Sub