View Full Version : Cell To show Data rather than a reference link

07-29-2004, 10:04 AM
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?


Zack Barresse
07-29-2004, 10:07 AM
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

07-29-2004, 10:18 AM
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.


07-29-2004, 11:51 AM
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
End Sub


07-29-2004, 06:53 PM
I "see" said the blindman. Matt...I think that will work. thanks a million


07-30-2004, 05:49 AM
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!