The code below creates a copy of the original workbook but only contains specific sheets. The saved name is created from the code but allows the user to select what folder and path they prefer. The saved copy contains links that requires the user when opening the copy to select update or ignore. I would like to remove any links when its saved and eliminate this popup when users open the file. I tried inerting the ListObject.Unlink command, but no luck. Can someone help me with this?
Sub NewBook()
'
' NewBook Macro
'
Sheets(Array("By Location", "By Parent Customer", "By Location & Customer", _
"AR over 270 Days", "Totals For CPM Load", "AR_Data_Work_Area")).Select
Sheets("By Location").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets(Array("By Location", "By Parent Customer", "By Location & Customer", _
"AR over 270 Days", "Totals For CPM Load", "AR_Data_Work_Area", "Terms")).Select
Sheets("By Location").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets(Array("By Location", "By Parent Customer", "By Location & Customer", _
"AR over 270 Days", "Totals For CPM Load", "AR_Data_Work_Area", "Terms")).Select
Sheets("Terms").Activate
Sheets(Array("By Location", "By Parent Customer", "By Location & Customer", _
"AR over 270 Days", "Totals For CPM Load", "AR_Data_Work_Area", "Terms")).Copy
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Dim IntialName As String
Dim sFileSaveName As Variant
Dim dt As String
InitialName = "AR COMBINED REPORT "
dt = VBA.format((Date), "mm-dd-yyyy")
' ActiveWorkbook.SaveAs fileName:= _
' "Y:\Accounts Receivable Reports - Combined\Book14.xlsx", FileFormat:= _
' xlOpenXMLWorkbook, CreateBackup:=False
sFileSaveName = Application.GetSaveAsFilename(InitialName & dt, "Excel Workbook (*.xlsx), *.xlsx")
If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
End If
'ungroup sheets in original
Windows("AR COMBINED REPORT MACRO TEMPLATEv5.0.xlsm").Activate
Sheets("AR over 270 Days").Select
End Sub
Bookmarks