Thread: Using Excel for Generating Unique Reports

Hi All
I have an excel question which i dont know where to begin so any help or guidance is very much appreciated.

I have an excel worksheet that list the Users Names, Email Address and the Dept they belong to. We are required to send out to each dept their list of users for their respective Head of Departments verification annually.

I have created a pivot table that we can select the relevant depts and the users list would be populated automatically.

My problem is how do i create a macro that can automatically cycle through all the depts in my list and export individual excel worksheet out as separate workbooks? Each workbook need to be renamed as their dept name.

Any ideas is appreciated.....

Based on your sample file - something like the below perhaps ?

Code:

Public Sub CreateDeptFiles()
Dim PT As PivotTable, PTF As PivotField, PTI As PivotItem
On Error GoTo ExitPoint
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False
End With
Set PT = Sheets("REPORT").PivotTables(1)
Set PTF = PT.PageFields("DEPT")
For Each PTI In PTF.PivotItems
    If PTI.Value <> "(blank)" Then
        PTF.CurrentPage = PTI.Value
        PT.TableRange2.Copy
        Sheets.Add(After:=Sheets(Sheets.Count)).Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
        With ActiveSheet
            .UsedRange.EntireColumn.AutoFit
            .Name = Range("B1").Value
            .Copy
            .Delete
        End With
        With ActiveWorkbook
            .SaveAs (Replace(ThisWorkbook.FullName, ThisWorkbook.Name, ActiveSheet.Name & ".xls", 1, 1))
            .Close
        End With
    End If
Next PTI
MsgBox "Files Created Successfully", vbInformation, "Complete"
ExitPoint:
Set PTF = Nothing
Set PT = Nothing
With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .EnableEvents = True
End With
End Sub

the above, when run, will iterate each dept and create a new file with a copy of the PT output - each file will be created within the same directory as the "master" file (ie the file in which the code is placed) and each file will be named as per the dept it contains.