macro to disconnect data connections when copying a sheet from master to standalone wrkbk

[ansvk1]

Hi there,
I have a code for creating a separate worksheet for any active sheet out of a master workbook that has data connections in it. now i am looking for a way to insert macro code that can:

1. make sure this new workbook being created is matching the original format
2. disconnecting all the data connections and bringing only values for those connections

Here is my code:

Sub Button1_Click()


    Dim ws As Worksheet
    Dim wbNew As Excel.Workbook, wbCurrent As Excel.Workbook
    Dim strFileName As String


    'Assign object variables
    Set wbCurrent = ActiveWorkbook
    Set ws = wbCurrent.ActiveSheet


    'Get desired file path from user
    strFileName = InputBox("Enter File Name: ", "Creating New File...")
    If strFileName <> "" Then
        
    'copy whole current sheet but with format intact & disconneting data connections, while bringing just their values over
        ws.Copy
        myFileName = wbCurrent.Path & "\" & strFileName & ".xlsx"
        ActiveWorkbook.SaveAs wbCurrent.Path & "\" & strFileName
    End If
End Sub

thanks for your help!

 

[ansvk1]

Ok thanks!
i will try it out Monday morning when i get to my PC and let you know how it went.