Morning Norman

That replacement works, but I still am not capturing the values in th
new Site Stats sheet just "#NAME?" for each entry. Hmmmm, I have trie
rearranging sequence etc but keep arriving at the same conclusion
which is that the values are being pasted before the link to othe
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 Repor
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 Su

--
Kstalke
-----------------------------------------------------------------------
Kstalker's Profile: http://www.excelforum.com/member.php...fo&userid=2469
View this thread: http://www.excelforum.com/showthread.php?threadid=46614