Lets see if this stumps you. I need some code to list the contents of 1

[addicted2]

Lets see how you go here. I need some code that will collect all the worksheet names from several workbooks that reside in serveral subfolders That are in a folder on C: called house hold. Sorry but i can think of no easier way to explain it. If you refer back to my last thread posted 2 days ago hopefully this will clear up any confusion I may have created.

I have a list of all the SubFolders
I have a list of all the xls's that are in these sub folders
now I need to get a list of all the worksheets and place them in the activecell of the activeworkbook.

Msg to Skip.Thanks may little program is in disoray now .joke.. How I hope to get around the duplicates is to add the users intitials when saving. Skip also there will be a seaarch done prior to entry. This search will focus on the lists that WE have been working on.

 

[SkipVought]

Tony,
Start with your list of...

Folder    File

A. Make a Pivot Table to summarize the list of Files...
1. Menu Item - Data/Pivot Table and Chart...
2. Where is the data...Option button - MS Excel list or database
3. What kind of report... Option button - PivotTable
4. Next
5. Where is the data... change Range to absolute column reference...$A:$B
6. Next
7. Where do you want to put... Option button - New worksheet
8. Layout
9. Drag File to Row, doubleclick File, select Subtotals - Option button - None OK
10. Drag File (from right) to Data
11. OK - Finish

Now you have a list of unique file names. Whenever the sorce data changes, all you need to do on the sheet with the Pivot Table is Data/Refresh Data.

Hope this helps ;-) Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

Here's how to add items to your ListBox from a Pivot table...

Sub AddListItems()
    For Each PivotTable In ActiveSheet.PivotTables
        For Each PivotField In PivotTable.PivotFields
            Select Case PivotField.Name
                Case "Files"
                    For Each PivotItem In PivotField.PivotItems
                        UserForm1.ListBox1.AddItem PivotItem.Name
                    Next
            End Select
        Next
    Next
End Sub

Hope this helps Skip,
metzgsk@voughtaircraft.com