Unanswered: Using VBA to count number of PT fields
Not sure if this is possible, I currently have code that will take each item name visible in a pivot table field and name the sheet after each pivot item name. I am trying to come up with code that will move each of the created sheets on to a separate workbook using the move function. The problem is that i would need to use an array formula in order to move the sheets efficiently. Has anybody out there done something similar? My brain is fried as far as figuring out how to do this.
The problem I was having with that is going back and forth between the parentworkbook and the newly created workbook as the calculations in the new workbook were based on pivot tables located in the parent workbook. I also felt it would be more efficient to move the sheets that needed to be moved all at once as opposed to one at a time. This is what I have so far, not quite what I wanted, but it works:
Dim ws As Worksheet, ss As Worksheet, Wb As Workbook
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Test" And ws.Name <> "PALs" And ws.Name <> "Sheet2" Then
If Wb Is Nothing Then
Set Wb = ActiveWorkbook
Set ss = ActiveSheet
Application.ScreenUpdating = True
This was based on code I found on OZGrid.com. If anybody knows of a more efficient way, i'd be more than inclined to listen.