Hi Kristan,

Try replacing:

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

with:

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

---
Regards,
Norman



"Kstalker" wrote in
message ...

Norman

Works a treat, thanks for the input. Read the link to Chip Pearsons
site and it is valuable information.

The copy is working well and there is one more thing I need it to do,
the 'Site Stats' sheet that is being copied contains links to other
data sources so I need to paste the values while maintaining the
colours and formatting. Can you shed any light on my novice coding????

srcWB.Sheets("Site Stats").Range("a1:az300").Copy _
After:=destWB.Sheets(destWB.Sheets.Count)
Range("A1").PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


thanks

Kristan



Have tried several

Public Sub testing()

Dim srcWB As Workbook
Dim destWB As Workbook

Set srcWB = ActiveWorkbook

srcWB.Sheets("CSB_Daily_Summary_cust_dmd_ver").Cop y

Set destWB = ActiveWorkbook

destWB.Colors = srcWB.Colors

With ActiveSheet.Range("AM2:AX185")

End With

ActiveSheet.Range("A1").Select

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")

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

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

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