Export worksheets to new file VBA to 'Break Links' on export?

[Spoonman667]

Hi Guys,

I'm using this code to export 3 select worksheets in my workbook to a new file which in essence works great.

Sub ExportNew()

Dim FileName As String
Dim Path As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Path = "C:\This location\"
FileName = "New File " & Range("A1").Value & ".xlsx"

Sheets(Array("Data", "Pivot", "Mapping")).Copy

With ActiveWorkbook
.SaveAs Path & FileName, xlOpenXMLWorkbook
End With
ActiveWorkbook.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

The problem I'm having now though is that when the sheets are exported, as the 3 sheets refer to eachother (data on one sheet, mapping on the 2nd and pivot on the other), the new file ends up with formula links to the original workbook, which is not ideal.

Does anyone have any suggestions how I can resolve this issue with VBA or otherwise?

Thanks,


Spoonman

[AlphaFrog]

Sub ExportNew()
    
    Dim FileName  As String
    Dim Path      As String
    Dim ws        As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Path = "C:\This location\"
    FileName = "New File " & Range("A1").Value & ".xlsx"
    
    Sheets(Array("Data", "Pivot", "Mapping")).Copy
    
    With ActiveWorkbook
        For Each ws In .Worksheets
            ws.UsedRange.Value = ws.UsedRange.Value
        Next ws
    
        .SaveAs Path & FileName, xlOpenXMLWorkbook
        .Close False
    End With
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

[Spoonman667]

Thanks AlphaFrog,

I've tried running the above code and i get an error on

 ws.UsedRange.Value = ws.UsedRange.Value

Thanks

[AlphaFrog]

What's the error description?

[Spoonman667]

It says "Run-time error '1004' We can't make this change for the selected cells because it will affect a pivot table. Use the field list to change the report. If you are trying to insert or delete cells, move the pivot table and try again.

It's actually half working now, it creates the new file but saves it as 'book1' instead of the name and cell reference as it's suppose to.

Also I think I see the problem now, the links are broken in the formulas but the pivot is still referencing the original workbook.

[AlphaFrog]

try this...

Sub ExportNew()
    
    Dim FileName  As String
    Dim Path      As String
    Dim ws        As Worksheet
    Dim pt        As PivotTable
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Path = "C:\This location\"
    FileName = "New File " & Range("A1").Value & ".xlsx"
    
    Sheets(Array("Data", "Pivot", "Mapping")).Copy
    
    With ActiveWorkbook
        For Each ws In .Worksheets
            For Each pt In ws.PivotTables
                pt.TableRange2.Copy
                pt.TableRange2.PasteSpecial Paste:=xlPasteValues
            Next pt
            ws.UsedRange.Value = ws.UsedRange.Value
        Next ws
    
        .SaveAs Path & FileName, xlOpenXMLWorkbook
        .Close False
    End With
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

[Spoonman667]

Thanks,

This is much closer and may suffice as at least there are no links to the source workbook however it would be desirable if it could stay as a pivot as this just shows the pivot as a data table and gets rid of the functionality.

Maybe it's not possible to do this?

Thanks for all your help