Hi Kristan,

I could not replicate your experience.

I note that you speak of the new Site Stats sheet. What about the new
CSB_Daily_Summary sheet, which is the first of the two sheets in the new
workbook? Are values correctly shown in this first sheet?

Both sheets in the new workbook are created in analagous manner: an entire
worksheet is copied and then the copied data is converted to values.

Given this, why do you need to break links? Data converted to its values
equvalent has no links.

It would appear that I am missing something fundamental to your situation.

For testing purposes, I set up a source book with a Daily Summary sheet
and a Site Stat sheet. Both sheets included formulae and links to a another
workbook.

I ran the code in this test environment without your observed problems. I
excluded the break links code line because of Excel version problems (my
test machine was set up for xl2k) and because I deemed this unnecessary -
see my links comments earlier.

---
Regards,
Norman



"Kstalker" wrote in
message ...

Morning Norman

That replacement works, but I still am not capturing the values in the
new Site Stats sheet just "#NAME?" for each entry. Hmmmm, I have tried
rearranging sequence etc but keep arriving at the same conclusion,
which is that the values are being pasted before the link to other
data locations within the source workbook is cut....

Will continue reading through forums, any advice greatly appreciated.

Cheers

Kristan

Public Sub testing()

Dim srcWB As Workbook
Dim destWB As Workbook

Set srcWB = ActiveWorkbook
'copy sheet into new workbook
srcWB.Sheets("CSB_Daily_Summary_cust_dmd_ver").Cop y

Set destWB = ActiveWorkbook

'copy the funky colours from the global report workbook
destWB.Colors = srcWB.Colors

'make the lookup section into values
With ActiveSheet.Range("AM2:AX185")
Value = .Value
End With

ActiveSheet.Range("A1").Select

'rename sheet
ActiveSheet.Name = "CSB_Daily_Summary " & Format(Date, "yyyymmdd")


srcWB.Sheets("Site Stats").Copy _
After:=destWB.Sheets(destWB.Sheets.Count)

ActiveSheet.Name = "Site Stats" & Format(Date, "yyyymmdd")

With ActiveSheet
Name = "Site Stats" & Format(Date, "yyyymmdd")
UsedRange.Value = .UsedRange.Value
End With

'break links
ActiveWorkbook.BreakLink Name:= _
"G:\ch\shared04\queue stats\Kristan Stalker\Global Report\Global Report
draft v3.0.xls", Type:=xlExcelLinks

'save in archive folder
ReportFilename = _
"G:\ch\shared04\queue stats\Kristan Stalker\Global Report " & _
Format(Date, "yyyymmdd") & ".xls"
destWB.SaveAs Filename:=ReportFilename

'close file
destWB.Close

End Sub


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=466141