Cell To show Data rather than a reference link

[Kicker]

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

[Zack Barresse]

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?

[Kicker]

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

[mvidas]

Hey Kicker,

Try the following sub:

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

[Kicker]

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

ttfn
Kicker

[mvidas]

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!