Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    25

    Unanswered: Using VBA to count number of PT fields

    Hello all,

    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.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hello,

    Why don't you create them directly in the separate workbook rather than creating them in the same workbook and then moving them? Why do you need an array formula to move the sheets efficiently?

  3. #3
    Join Date
    Mar 2005
    Posts
    25
    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:

    Sub MoveSheets()
    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
    ws.Move
    Set Wb = ActiveWorkbook
    Else
    ws.Move after:=ss
    End If
    Set ss = ActiveSheet
    End If
    Next ws

    Application.ScreenUpdating = True
    End Sub
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •