Excel Macro: Copy sheet without links - just data

Question

I would like to copy one specific sheet in MS Excel to a new file. The following Macro does the job, but the problem is that the copy always links the cells to the original file. Is there a way to insert only the values of this sheet without the links?

Sub Copy_sheet()

    ThisWorkbook.Sheets("Overview").Copy

    ActiveWorkbook.SaveAs "C:\testfolder\testfile.xlsx", FileFormat:=51

End Sub

Thank you!

Answer 1

Add the following to your code before the End sub:

With ThisWorkbook.Worksheets("Overview").UsedRange
    .Value = .Value
End With

It would change the formulas to values. If you want to do it for the whole workbook, this is the way to go:

Public Sub CopyJustData()

    Dim lngCount    As Long

    For lngCount = 1 To Worksheets.Count
        With Worksheets(lngCount).UsedRange
            .Value = .Value
        End With
    Next lngCount

End Sub

Answer 2

One way is this.

Sub Copy_sheet()

Dim wbNew As Workbook

ThisWorkbook.Sheets("Overview").Copy 
Set wbNew = ActiveWorkbook

With wbNew
    With .Worksheets(1).UsedRange
         .Value = .Value
    End With

    .SaveAs "C:\testfolder\testfile.xlsx"
End With
End Sub

Answer 3

Sub SaveCopy
    ThisWorkbook.Sheets("your sheet name").Select
    ActiveSheet.Copy
    ActiveSheet.SaveAs FileName:="your path to save "& ".xlsx" 'xlsx or your other
    BreakLinks ' 
End Sub

Sub BreakLinks()
    On Error Resume Next
    Dim vLinks As Variant
    Dim lLink As Long
 
    ' Define variable as an Excel link type.
    vLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    If vLinks = vbNullString Then Exit Sub
 
    ' Break all links in the active workbook.
    For lLink = LBound(vLinks) To UBound(vLinks)
        ActiveWorkbook.BreakLink _
                Name:=vLinks(lLink), _
                Type:=xlLinkTypeExcelLinks
    Next lLink
End Sub