Hey Hutch,

I was stuck while coding for looping a drop-down and I came across your post (https://groups.google.com/forum/#!to...ns/KCIQJzf0GeE) .
I was wondering if you could help me with a similar problem. I'm pretty new to excel and macro coding and I'd really appreciate it if you can help me out.

I need to create a macro while enables the user to do the following :

1. Browse for a file
2. Create a pivot table once the file is inserted.
3. From the drop down list that is generated in the pivot table, all the data from each item in the drop-down should be copied into a new sheet in the same workbook.
4. Each time I browse a file, the items in the drop down list might not be the same after the pivot is created. (Generalized)

The following is the code that I developed. I'd be really grateful if you can help me out with this :) .

Sub Macro4()
'
' Macro4 Macro
'

'
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Sheet1!R1C1:R6521C12", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable37", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet4").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable37").PivotField s("Activity Descr")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable37").PivotField s("Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable37").PivotField s("Acc Date")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable37").AddDataFie ld ActiveSheet.PivotTables( _
"PivotTable37").PivotFields("Hours"), "Sum of Hours", xlSum
ActiveSheet.PivotTables("PivotTable37").PivotField s("Activity Descr"). _
CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable37").PivotField s("Activity Descr")
.PivotItems("General").Visible = False
.PivotItems("Meetings/ Calls/ Proposals").Visible = False
.PivotItems("Scheduled But not Utilized").Visible = False
.PivotItems("Training").Visible = False
End With
ActiveSheet.PivotTables("PivotTable37").PivotField s("Activity Descr"). _
EnableMultiplePageItems = True
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Client Work"
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet4").Select
ActiveSheet.PivotTables("PivotTable37").PivotField s("Activity Descr"). _
CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable37").PivotField s("Activity Descr")
.PivotItems("ClientWork").Visible = False
.PivotItems("Meetings/ Calls/ Proposals").Visible = False
.PivotItems("Scheduled But not Utilized").Visible = False
.PivotItems("Training").Visible = False
.PivotItems("General").Visible = True
End With
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "General"
Sheets("Sheet4").Select
ActiveSheet.PivotTables("PivotTable37").PivotField s("Activity Descr"). _
CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable37").PivotField s("Activity Descr")
.PivotItems("General").Visible = False
.PivotItems("Meetings/ Calls/ Proposals").Visible = True
End With
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Meeting Calls Proposals"
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet4").Select
ActiveSheet.PivotTables("PivotTable37").PivotField s("Activity Descr"). _
CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable37").PivotField s("Activity Descr")
.PivotItems("Meetings/ Calls/ Proposals").Visible = False
.PivotItems("Scheduled But not Utilized").Visible = True
End With
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "Scheduled but not utilized"
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet4").Select
ActiveSheet.PivotTables("PivotTable37").PivotField s("Activity Descr"). _
CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable37").PivotField s("Activity Descr")
.PivotItems("Scheduled But not Utilized").Visible = False
.PivotItems("Training").Visible = True
End With
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet7").Select
Sheets("Sheet7").Name = "Training"
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Scheduled but not utilized").Select
Range("A1").Select
Sheets("Meeting Calls Proposals").Select
Range("A1").Select
Sheets("General").Select
Range("A1").Select
Sheets("Client Work").Select
Range("A1").Select
Sheets("Sheet1").Select
Range("A1").Select
Sheets("Sheet4").Select
Range("A1").Select
End Sub





This is a specific macro . I'm looking for something that will work for any xlsx file (all with same column headers)