External links causing another workbook to open

[Obewan]

I have a macro enabled workbook which I copied all the formulas from another workbook and a couple of macros. I have thoroughly gone through all the formula's and changed all formulas so there should be no links. Every time I open the new workbook it still says I have links. Every time I run one of the macros it opens the old workbook which takes time as it is quite a large file. I tried to install findlinks - FindLink - but couldn't get it to install. Any suggestions?

I have done all the search for [ and xls so I'm pretty sure there are no formulas with external links. Could the macro have a link in it?

Thanks

 

[rlv01]

Are you using conditional formatting or cell validation? If you are, inspect them carefully for links. Any links in there are very hard to find or delete unless you know they are there and go looking.

 

[Obewan]

I have tried that but not working. It's weird because even when I open the book and choose not to update links when I run the macro it opens the old book.

 

[rlv01]

...when I run the macro it opens the old book.

That seems like a clue. What happens when you use the VBE to single step through the macro? Can you spot the line of code that causes the old WB to open?

 

[Obewan]

No - maybe you can, here it is:

Sub FullFormRun()

'

' FullFormRun Macro

'



'

Sheets("F").Select

Columns("I:I").Select

Application.CutCopyMode = False

Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True

Selection.NumberFormat = "0.0"

Selection.NumberFormat = "0"

Rows("1:1").Select

Selection.AutoFilter

ActiveSheet.Range("$A$1:$I$2153").AutoFilter Field:=7, Criteria1:="<>0", _

Operator:=xlAnd

ActiveSheet.Range("$A$1:$I$2153").AutoFilter Field:=6, Criteria1:=">0", _

Operator:=xlAnd

Columns("A:I").Select

Range("C1").Activate

Selection.Copy

Sheets("Days").Select

Range("A1").Select

ActiveSheet.Paste

Sheets("F").Select

Application.CutCopyMode = False

Selection.AutoFilter

Sheets("Filter").Select

Columns("A:H").Select

Range("H1").Activate

Selection.ClearContents

Sheets("F").Select

Rows("1:1").Select

Selection.AutoFilter

ActiveSheet.Range("$A$1:$IV$3000").AutoFilter Field:=7, Criteria1:="<>0", _

Operator:=xlAnd

ActiveSheet.Range("$A$1:$IV$3000").AutoFilter Field:=8, Criteria1:="<>F", _

Operator:=xlAnd

Columns("A:H").Select

Selection.Copy

Sheets("Filter").Select

Range("A1").Select

ActiveSheet.Paste

Range("K2").Select

Sheets("F").Select

Selection.AutoFilter

Sheets("Filter").Select

Sheets("Calculations").Select

Rows("1:1").Select

Range("D1").Activate

Selection.AutoFilter

ActiveSheet.Range("$A$1:$V$5000").AutoFilter Field:=7, Criteria1:=">0", _

Operator:=xlAnd

ActiveWindow.SmallScroll ToRight:=4

ActiveSheet.Range("$A$1:$V$5000").AutoFilter Field:=22, Criteria1:="<>0", _

Operator:=xlAnd

ActiveWindow.SmallScroll ToRight:=-4

Columns("B:I").Select

Range("I1").Activate

Selection.Copy

Sheets("Distance").Select

Range("B1").Select

ActiveSheet.Paste

Sheets("Calculations").Select

Application.CutCopyMode = False

Selection.AutoFilter

Sheets("Filter").Select

Sheets("Bet sheet").Select

Rows("2:2").Select

Selection.AutoFilter

ActiveSheet.Range("$A$2:$BY$242").AutoFilter Field:=28, Criteria1:="<>"

Rows("3:243").Select

Selection.Copy

With Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)

.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

.PasteSpecial Paste:=xlPasteFormats

Sheets("Bet sheet").Select

Application.CutCopyMode = False

Selection.AutoFilter

Sheets("Formguide").Select



End With

End Sub

 

[Fluff]

How are you calling the macro that opens the old workbook?

 

[Obewan]

Using a button

 

[Fluff]

In that case check that the button is not pointing to the old file.

 

[Obewan]

How are you calling the macro that opens the old workbook?

Good call!! I just added a new button and deleted the old one and it works!!

 

[Fluff]

Glad we could help & thanks for the feedback.

Also when posting code please use code tags How to Post Your VBA Code it makes it a lot more readable.