Cell To show Data rather than a reference link

I know it can be done but have just ha a brain f%# (Gas).

I have a worksheet that has over 100 cells linked to another workbook that is not open. I NEVER want to "update" the data once it is read the first time. What is the easiest way to insert the data referenced rather than the link?

ttfn
Kicker

Copy, Paste Special / Values. With so many, it kind of depends on how your data is structured. I guess we couldn't get lucky enough that the sheets are all in order with the formula(s) in the same cell address? :dunno

Unfortunately NO.

Here is basically what I have.

Master workbook
Shift 1 workbook
shift 2 workbook
Shift 3 workbook

Each shift workbook will have 14 worksheets with specific names depending on the date. Cols A, D, and E (out of A-J) are important to the Master workbook.

Daily (or periodically), I will need to get the information from each shift workbook and put it in a different location in the master workbook.

I already have it working with finding and copying ranges and the paste special into the master workbook. I was just hoping that I could do it an easier way.

ttfn
Kicker

Hey Kicker,

Try the following sub:

Code:

---

Sub StopSpecificExternalLink()
Dim cL As Range
Dim extLinkDir As String, extLinkFileName As String, extLink As String
'Either of these two variables can be blank, but not both
extLinkDir = "C:\temp\"
extLinkFileName = "filename.xls"
If extLinkDir = "" And extLinkFileName = "" Then Exit Sub
If extLinkDir <> "" Then
    If Right(extLinkDir, 1) <> Application.PathSeparator Then
        extLinkDir = extLinkDir & Application.PathSeparator
    End If
End If
If extLinkFileName <> "" Then extLinkFileName = "[[]" & extLinkFileName & "[]]"
extLink = "*" & extLinkDir & extLinkFileName & "*"
For Each cL In ActiveSheet.UsedRange.Cells
    If UCase(cL.Formula) Like UCase(extLink) Then cL = cL.Value
Next
End Sub

---

Matt

I "see" said the blindman. Matt...I think that will work. thanks a million

ttfn
Kicker

Not a problem, I'm sure there are faster ways to do it, but that was the best I could think of. Let me know if you need any help with it!