locked
Copying sheets(Pivot and data) to new workbook, but the pivot refering the old workbook data RRS feed

  • Question

  • Hi,

    I have a list of sheets in my workbook. I am creating two new workbook for reporting.when I am copying the sheets to new workbook which includes pivot also. the new workbook as pivot which is referring data in old worksheet.

    Can one please help me to over cum this

    I have same sheet name and reference columns in new workbook.  I can't move the sheets  to new workbook because, the two new workbook need base data sheet. Please see below code for reference.

    path1 = ThisWorkbook.Path & "Test1.xlsx"
    path2 = ThisWorkbook.Path & "Test2.xlsx"
    
        
    Sheets(Array( "Summary","PivotforOrg",  "PivotforBU", "Basedata", "Lookup")).Copy
    ActiveWorkbook.SaveAs Filename:=path1, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWindow.Close
        
    Sheets(Array("Summary", "Consolidated_View", "PivotforMangment", "PivotforLeadlevel","Basedata", "Lookup")).Copy
    ActiveWorkbook.SaveAs Filename:=path2, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWindow.Close

    Wednesday, April 27, 2016 1:16 PM

Answers

  • Two ways:

    1) The macros don't need to reside in the template. You can have two files - one with the macro (.xlsm), one with the sheets (.xlsx). Then you operate using .xlsx files only.

      Path1 = ThisWorkbook.Path & "\Test1.xlsx"
        Path2 = ThisWorkbook.Path & "\Test2.xlsx"

        Set AWB = Workbooks.Open(ThisWorkbook.Path & "\Template without macros.xlsx")

        Application.DisplayAlerts = False

        AWB.SaveCopyAs Path1

        Set WB = Workbooks.Open(Path1)

        etc...

    2) The SaveCopyAs creates an .xlsm (which is the same type as your template). Open the .xlsm copy, then do a SaveAs, changing the type to .xlsx, and delete the .xlsm file (using Kill).

        path1 = ThisWorkbook.Path & "\Test1.xls"
        Path2 = ThisWorkbook.Path & "\Test2.xls"

        Set AWB = ActiveWorkbook

        Application.DisplayAlerts = False

        AWB.SaveCopyAs path1 & "m"

        Set WB = Workbooks.Open(path1 & "m")
        WB.SaveAs path1 & "x", xlOpenXMLWorkbook
        Kill path1 & "m"
        Set WB = Workbooks.Open(path1 & "x")

       'etc.....

    • Marked as answer by usapnew Thursday, May 12, 2016 8:16 AM
    Wednesday, May 11, 2016 1:17 PM

All replies

  • I would open the file with the working pivot table, save a copy of it with a new name, open the copy, delete the unneeded sheets, then copy over any new data that is needed.
    Wednesday, April 27, 2016 3:04 PM
  • I have template to run the macro to produce two workbook.Based on your suggestion how can I save my current template.can you please post some sample code?
    Thursday, May 5, 2016 1:47 PM
  • This assumes that the code is in a macro-enabled .xlsm file, and your current template is the activeworkbook

    Sub TestMacro()
        Dim Path1 As String
        Dim Path2 As String
        Dim WB As Workbook
        Dim AWB As Workbook
        Dim WS As Worksheet

        Path1 = ThisWorkbook.Path & "\Test1.xlsx"
        Path2 = ThisWorkbook.Path & "\Test2.xlsx"

        Set AWB = ActiveWorkbook

        Application.DisplayAlerts = False

        AWB.SaveCopyAs Path1

        Set WB = Workbooks.Open(Path1)

        For Each WS In WB.Worksheets
            If InStr("Summary PivotforOrg PivotforBU Basedata Lookup", WS.Name) = 0 Then WS.Delete
        Next WS
        WB.Close True

        AWB.SaveCopyAs Path2

        Set WB = Workbooks.Open(Path2)

        For Each WS In WB.Worksheets
            If InStr("Summary Consolidated_View PivotforMangment PivotforLeadlevel Basedata Lookup", WS.Name) = 0 Then WS.Delete
        Next WS
        WB.Close True

        Application.DisplayAlerts = True

        AWB.Close
    End Sub

                                                
    Friday, May 6, 2016 2:12 PM
  • Thanks for the code,it is working.

    However how can I save the template(with macro) as .xlsx ?

    when opening the workbook

    Set WB = Workbooks.Open(Path1) it is showing me the error.

    Run-time error '1004'. Excel cannot open the file 'Test1.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

    How can I remove the macro in copied workbook?
    • Edited by usapnew Wednesday, May 11, 2016 9:39 AM
    Wednesday, May 11, 2016 9:37 AM
  • Two ways:

    1) The macros don't need to reside in the template. You can have two files - one with the macro (.xlsm), one with the sheets (.xlsx). Then you operate using .xlsx files only.

      Path1 = ThisWorkbook.Path & "\Test1.xlsx"
        Path2 = ThisWorkbook.Path & "\Test2.xlsx"

        Set AWB = Workbooks.Open(ThisWorkbook.Path & "\Template without macros.xlsx")

        Application.DisplayAlerts = False

        AWB.SaveCopyAs Path1

        Set WB = Workbooks.Open(Path1)

        etc...

    2) The SaveCopyAs creates an .xlsm (which is the same type as your template). Open the .xlsm copy, then do a SaveAs, changing the type to .xlsx, and delete the .xlsm file (using Kill).

        path1 = ThisWorkbook.Path & "\Test1.xls"
        Path2 = ThisWorkbook.Path & "\Test2.xls"

        Set AWB = ActiveWorkbook

        Application.DisplayAlerts = False

        AWB.SaveCopyAs path1 & "m"

        Set WB = Workbooks.Open(path1 & "m")
        WB.SaveAs path1 & "x", xlOpenXMLWorkbook
        Kill path1 & "m"
        Set WB = Workbooks.Open(path1 & "x")

       'etc.....

    • Marked as answer by usapnew Thursday, May 12, 2016 8:16 AM
    Wednesday, May 11, 2016 1:17 PM
  • Thanks for the code and time - Deitrick. Perfect.
    Thursday, May 12, 2016 8:16 AM