I am linking a portion of my worksheet to another workbook and using a
macro to remove the 0s from the current worksheet.

Here's the macro:

Sub Blank_Link_Trap()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(" & myStr & "="""",""""," & myStr &
")"
End If
End If
Next
End Sub

I copied this from a different thread. Every time I run this macro on
the linked text, I have to choose the correct linked workbook for
every single cell. A box comes up and says "cannot find" and forces me
to find the linked workbook and click on Copy.

Any ideas how I can get around this?

What is the current sheet?

Works for me on target workbook and sheet with source workbook closed.


Gord Dibben MS Excel MVP

On Tue, 22 Jul 2008 15:49:29 -0700 (PDT), stephanieleith@gmail.com wrote:

>I am linking a portion of my worksheet to another workbook and using a
>macro to remove the 0s from the current worksheet.
>
>Here's the macro:
>
>Sub Blank_Link_Trap()
>Dim myStr As String
>Dim cel As Range
> For Each cel In Selection
> If cel.HasFormula = True Then
> If Not cel.Formula Like "=IF(*" Then
> myStr = Right(cel.Formula, Len(cel.Formula) - 1)
> cel.Value = "=IF(" & myStr & "="""",""""," & myStr &
>")"
> End If
> End If
> Next
>End Sub
>
>I copied this from a different thread. Every time I run this macro on
>the linked text, I have to choose the correct linked workbook for
>every single cell. A box comes up and says "cannot find" and forces me
>to find the linked workbook and click on Copy.
>
>Any ideas how I can get around this?

Could it be because I am on a mac?

The macro works if I use it on linked cells from sheets within the
same workbook. But when I link to another workbook, open or closed, it
still forces me to "find" the workbook for each cell.

In essence, the dependent worksheet has information linked from the
first sheet of the dependent workbook and information linked from a
source workbook. There's a whole lot of linking going on. The first
sheet is a schedule that is copied to all other worksheets in the book
and then each sheet has information linked from source workbooks
updated by other departments, hosted on a server.