I would like to write a macro that starts in the Pivot Table (TransPiv) and goes through each row and performs Selection.ShowDetail=True, takes the resultant new sheet, and saves that sheet in a new workbook to a specific location. It then deletes the sheet in the 'Master' sheet, and performs this for each row in the Pivot Table.


My code so far:

Set Bran = Range(Range("TransPiv"), Cells(Range("TransPiv").End(xlDown).Row, Range("TransPiv").End(xlToRight).Column))
FileDir = Range("FileDir").Value

For i = 2 To Bran.Rows.Count
CurBro = Bran(i, 1)
CurTot = Bran(i, 2)
If CurBro <> "(blank)" Then

Selection.ShowDetail = True

Filenam = CurBro & "_Remittance_" & Application.WorksheetFunction.Text(Range("PayDate").Value, "yyyymmdd")
dC = 0
dT = 0

For k = 1 To TransR.Rows.Count
If TransR(k, 2) = CurBro Then
TransR(k, 2).EntireRow.Copy
TransO.Offset(dT, 0).PasteSpecial xlPasteAll
dT = dT + 1
End If

Range("PayTot").Value = CurTot

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=FileDir & Filenam & ".xlsx", FileFormat:=51