YTL Excel #114: Copy Table & Name to New Workbook

Hi Folks:

I have a need to copy a worksheet to a new workbook, but only the pivot table. No VBA code, and no buttons, headings, or other objects.

I am open to suggestions. I can create a new sheet in the current workbook and then save that sheet, but I can't find a reference of how to copy just the pivot table.

Hey - like I said, I am open to suggestions, my initial thought was to just take the data out of the pivot table, and save it as a Sharepoint List but I haven't figured that out.

so ... my questions:

How to copy a pivot table to a new sheet.

Thanks

Don

Hey guys... I'm still searching for solution that will allow me copy a pivot table from an Excel 2007 workbook and paste the static results (not linked) into a new workbook, maintaining the format of the pivot table. I just spent the last hour and a half pasting values into a new workbook and coloring cells and bolding Totals values. With 2003 I thought I remembered that I pasted all then pasted values and that maintained the format of the table with static results. I've got to believe there is something easy like that for 2007.

BTW, Microsoft "Support" told me it would cost $259 for them to reveal the answer to this question - I really appreciate all of you and your assistance.

I am trying to copy a pivot table into another workbook but I loose all the features I had in my original workbook when I do copy and paste method. Is there a way to copy the pivot table from one workbook into another workbook without loosing all the pivot features like moving around the attributes either to the row or to the page field etc? Thanks.

Can anyone tell me where Excel generates pivot table data, once you have copied a pivot table to a new workbook.

eg. I have a data set in Excel, some 9,000 rows, 10 Columns. I create a Pivot table using all of the data then copy that worksheet to a new book. Where is the new book pulling the pivot table data from?

Will the pivot table still function if I save and send it to someone outside my network?

O im using Excel 2007.

Hi there,

I need a macro to automatically hit the 'tab' key when the workbook closes.

The idea is that I have 2 workbooks: The first workbook is the invoice that is manually filled out with customer information and the second workbook is the database of the information. The first workbook automatically transfers the information to the second workbook with this code:


VB:

Sub Copy_Stuff() 
     
    Dim wbMasterBook As Workbook 
    Dim wsMasterSheet As Worksheet 
    Dim lngWriteRow As Long 
     
    Set wbMasterBook = Workbooks.Open("C:\Users\Luis\Desktop\SCC\Excels\Cust Info.xlsx") 
    Set wsMasterSheet = wbMasterBook.Sheets("February") 
     
    lngWriteRow = wsMasterSheet.Range("A1").End(xlDown).Offset(1, 0).Row 
     
    With ThisWorkbook.Sheets("Sheet1") 
        wsMasterSheet.Range("A" & lngWriteRow) = .Range("F1") 
        wsMasterSheet.Range("B" & lngWriteRow) = .Range("B10") 
        wsMasterSheet.Range("C" & lngWriteRow) = .Range("B11") 
         
        wsMasterSheet.Range("D" & lngWriteRow) = .Range("B12") 
        wsMasterSheet.Range("E" & lngWriteRow) = .Range("B13") 
        wsMasterSheet.Range("F" & lngWriteRow) = .Range("B14") 
         
        wsMasterSheet.Range("G" & lngWriteRow) = .Range("A17") 
        wsMasterSheet.Range("H" & lngWriteRow) = .Range("B17") 
        wsMasterSheet.Range("I" & lngWriteRow) = .Range("D17") 
         
        wsMasterSheet.Range("J" & lngWriteRow) = .Range("F17") 
        wsMasterSheet.Range("K" & lngWriteRow) = .Range("H17") 
        wsMasterSheet.Range("L" & lngWriteRow) = .Range("H85") 
         
        wsMasterSheet.Range("M" & lngWriteRow) = .Range("H86") 
        wsMasterSheet.Range("N" & lngWriteRow) = .Range("F3") 
        wsMasterSheet.Range("O" & lngWriteRow) = .Range("C23") 
         
        wsMasterSheet.Range("P" & lngWriteRow) = .Range("F85") 
    End With 
     
    wbMasterBook.Close True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



I made the database in the second workbook go into a table and I need it to press 'tab' before it closes so that a new row will automatically be made in the table right before the second workbook closes.

Thanks in advance!

-Luis

EDIT: I just realized the data doesn't copy over to the table but it copies over without the table. The code is still the same. Any help will be highly appreciated. Thank you!

Dear All

How can i copy a pivot table to a new workbook so that its size is reduced.

Rajesh Chandra

Hi Group

I use the following code to move each sheet into its own workbook, paste as values, however this month it has stopped working with pivot tables. Any one know a work around.?

I get the following run time error 1004

Private Sub CommandButton1_Click()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
With ActiveSheet.UsedRange
.Value = .Value
End With

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\dir\" & w.Name

ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Hi,

I'm struggling to copy a pivot table to a new workbook and worksheet. The code below creates the pivot table;

Code:

Sub Macro3()
'
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "DATA!A1:R50000").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable2", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Proj")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Activity")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Week Comm")
        .Orientation = xlColumnField
        .Position = 1
    End With
    'With ActiveSheet.PivotTables("PivotTable2").PivotFields("Year")
    '    .Orientation = xlColumnField
    '    .Position = 1
    'End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Quantity"), "Sum of Hours", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Amount"), "Sum of Mat Costs", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.Name = "pivot data sheet"
End Sub

but this code is failing once it gets past the creation of new workbook/worsheet stage;

Code:

Sub CreateBlankWorkbook()

Dim PT As PivotTable
Dim PTCache As PivotCache
Dim WSR As Worksheet
Dim WBN As Workbook

Set WBN = Workbooks.Add(xlWBATWorksheet)
Set WSR = WBN.Worksheets(1)
WSR.Name = "Report"
With WSR.[A1]
    .Value = "New Report"
    .Font.Size = 20
End With

ActiveSheet.PivotTables("PivotTable2").Copy
WSR.[A3].PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'PT.TableRange2.Clear
Set PTCache = Nothing

End Sub

Can anyone help please?

Thanks,

Louise

I need code that will open a different excel workbook off of my drive and copy and paste data from it into a workbook that I am working in and then close the data workbook.