List all worksheets (and Document Names)
here's something you can start with. i have hard coded a path variable and
have the filenames listed starting in b1 on sheet2.
this opened all the files list on sheet2 and put the filename in a2 under
book and the sheets starting in b2 under sheet heading
see if you can adapt it
Option Explicit
Dim fPath As String
Dim fname As String
Dim SH As Worksheet
Dim rng As Range
Dim i As Long
Dim WB1 As Workbook
Dim lastrow As Long
Dim CurBook As String
Dim e As String
Dim lFname As Long
Sub ListAll()
CurBook = Application.ThisWorkbook.Name
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "a").End(xlUp).Row
i = 1
fPath = "N:\My Documents\Excel\RECCU\FSA\"
lFname = Worksheets("Sheet2").Cells(Rows.Count, "b").End(xlUp).Row
For i = 1 To lFname
fname = ThisWorkbook.Worksheets("Sheet2").Cells(i, "b").Value
Workbooks.Open Filename:=fPath & fname, ReadOnly:=True, UpdateLinks:=3
For Each SH In Worksheets
Workbooks(CurBook).Worksheets("sheet1").Range("a" & lastrow + 1) = _
ActiveWorkbook.Name
Workbooks(CurBook).Worksheets("sheet1").Range("B" & lastrow + 1).Value = _
UCase(SH.Name)
lastrow = lastrow + 1
Debug.Print SH.Name
Next SH
i = i + 1
Workbooks(fname).Close SaveChanges:=False
Next
End Sub
--
Gary
"Barb Reinhardt" wrote in message
...
I have about 80 workbooks that are supposed to have the same worksheet
names
in them, but I know that they don't. I want to gather a list of all of
the
worksheets in each workbook in one document. The workbooks are not all
stored in the same folder. I do have the URL for each workbook easily
available to me in an excel worksheet.
Any suggestions?
Thanks in advance,
Barb Reinhardt
|