How to avoid Update Links notification?
"E.J. van Wijngaarden" wrote in message
...
Hello experts,
In workbook A I add a worksheet using another workbook B as template, all
in
VBA code.
This workbook B contains references to named regions in workbook A, which
works fine once it is loaded.
Now I have the following problem: on adding the new worksheet Excel
notifies
that in workbook B there are links to external workbooks and asks if they
must be updated.
I want to get rid of this notification.
I set Application.Displayalerts to False before adding workbook B, but
this
has no effect on this notification.
I have the same scenario in some of my developments.
I get agound it by (in the new workbook) copying the cells with the formulas
you want to remove then pasting values. One looks like this:
Sub CreateOpsFile()
Application.ScreenUpdating = False
Sheets("Operations").Select
Sheets("Operations").Copy
Workbooks(Workbooks.Count).Activate
ActiveSheet.Unprotect
Range("A1").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Range("B3:B7").Copy
Range("B3:B7").PasteSpecial Paste:=xlPasteValues
Range("B11:L20").Copy
Range("B11:L20").PasteSpecial Paste:=xlPasteValues
Range("B22:L23").Copy
Range("B22:L23").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("A1").Select
ChDir "I:\Accounting\Daily Tonnes\DailyReports"
ActiveWorkbook.SaveAs Filename:= _
"I:\Accounting\Daily Tonnes\DailyReports\" & Range("Date") &
"-Ops.xls"
ActiveWindow.Close
MsgBox "File: I:\Accouting\Daily Tonnes\DailyReports\" & Range("Date") &
"-Ops.xls has been created", , "Daily Tonnes"
Sheets("Main").Activate
Range("Date").Select
Application.ScreenUpdating = True
End Sub
In this way I preserve the local formulas such as totalling columns but
remove the formulas referring to other worksheets/workbooks.
HTH
Damo
|