Automatically updating Tabs in a Template, but not affecting downloaded copies

[Bill Morris]

Hi

I have a template (Master) which has a number of tabs. Each tab is a copy of a tab in other Excel workbooks. If any of these are updated, then the tabs in the template update also as they are linked.

Users open/download the Master template, add in info, then Save As 'newfilename'.xlsx workbook.

How can I set it so that if changes are made to the tabs in the Master template, that it doesn't affect the 'newfilename'.xlsx file?

Thanks in advance

 

[Engberg]

How are they linked? Formulas? VBA? Other method?

 

[Bill Morris]

How are they linked? Formulas? VBA? Other method?

Hi

In the workbooks, I selected a large area on the page, CTRL+C, then Paste Link into the Master tab.

If there's a better way then I would be keen to learn how.

 

[Engberg]

In the area you are copying and pasting, is it always only static values or does it have to keep formulas as well when users Save As?

 

[Bill Morris]

There are no calculations/formula as such. Although there is some basic conditional formatting depending on what dropdown value is selected in the row.

It's essentially a load of columns with headers. If, say, 'option 1' is selected from a column 1 in row X, then it would grey out a cell in row X, column 3.

 

[Engberg]

Try running this macro in the Master Template. It will open a Save As dialog box, save a copy, open the copy, and convert all cell contents to values (this breaks the link to the master/other workbooks). Run it in a backup of your Master Template in case something goes wrong

Sub SaveAsAndBreakLinks()
Dim dialogResult As Variant

' --- SAVE AS DIALOG
dialogResult = Application.GetSaveAsFilename(FileFilter:= _
         "Excel Files (*.xlsm), *.xlsm", Title:="Save As and break data links", _
        InitialFileName:="newFile.xlsm")

' --- CHECK IF DIALOG IS CANCELED
If dialogResult <> False Then
    ActiveWorkbook.SaveCopyAs Filename:=dialogResult
Else
    Exit Sub
End If

' --- OPEN THE NEW WB, CONVERT ALL CELLS TO VALUES AND SAVE
Set newWb = Workbooks.Open(dialogResult)
Dim ws As Worksheet
For Each ws In newWb.Sheets
    ws.UsedRange.Value = ws.UsedRange.Value
Next
newWb.Save
End Sub