Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save Values to new worksheet (Excel Ver 11 (2003))

    I have a large worksheet with a pivot table.
    The pivot Table details customers and selecting a customer from the pivot populates an area of a worksheet with related data.

    I want to set up a procedure to loop through each of the pivot table items and for each customer, copy the the related data (as values) and paste to a new workbook (customername.xls). That way every time the procedure runs all the customer.xls files will be updated.

    As a start, I have some code from Woody's lounge to loop through a pivot table (I don't want to use the publish to html part).


    Sub Export()
    Dim Filename
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Set ws = ActiveSheet
    Set pt = ws.PivotTables(1)
    Set pf = pt.PageFields(1)
    ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
    For Each pi In pf.PivotItems
    pf.CurrentPage = pi.Name
    Filename = Range("m2")
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    pt.PivotCache.Refresh

    ' With ActiveWorkbook.PublishObjects.Add(xlSourceRange, "Cocuments and SettingsView1Desktop" & Filename
    ' & ".htm", "pivot", "$a$2:$m$50", xlHtmlStatic)
    ' .Publish (True)
    ' .AutoRepublish = False
    ' End With


    Next pi
    Set pi = Nothing
    Set pf = Nothing
    Set pt = Nothing
    Set ws = Nothing
    End Sub

    Can anyone show me the way forward on this one?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Values to new worksheet (Excel Ver 11 (2003))

    Welcome to Woody's Lounge!

    Does this do what you want?

    Sub Export()
    Dim strFilename As String
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim wb As Workbook

    Set ws = ActiveSheet
    Set pt = ws.PivotTables(1)
    Set pf = pt.PageFields(1)
    ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
    For Each pi In pf.PivotItems
    pf.CurrentPage = pi.Name
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    pt.PivotCache.Refresh
    strFilename = Range("M2")
    Set wb = Workbooks.Add(Template:=xlWBATWorksheet)
    ws.UsedRange.Copy
    With wb.Worksheets(1).Range("A1")
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
    End With
    Application.DisplayAlerts = False
    wb.Close SaveChanges:=True, Filename:=strFilename
    Application.DisplayAlerts = True
    Next pi

    Set wb = Nothing
    Set pi = Nothing
    Set pf = Nothing
    Set pt = Nothing
    Set ws = Nothing
    End Sub

  3. #3
    New Lounger
    Join Date
    Aug 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Values to new worksheet (Excel Ver 11 (2003))

    Thanks for such a quick response. That's works great, thanks.

    One small thing - my existing report is in Landscape format but that the copied reports default to portrait. Can this also be set by the code.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Values to new worksheet (Excel Ver 11 (2003))

    Add the following line just before the line that closes the new workbook:

    wb.Worksheets(1).PageSetup.Orientation = xlLandscape

Posting Permissions

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