Hi Kristan,

Have adapted etc and can't seem to get past the "Set destWB =
ActiveWorkbook".

(1) What error are you getting and what is highlighted when the error
occurs?
(2) Where is 'currentGlobalReportFile' declared and how is it dimmed?


---
Regards,
Norman



"Kstalker" wrote in
message ...

Cheers Norman.

Have adapted etc and can't seem to get past the "Set destWB =
ActiveWorkbook".

Unsure why this is occouring..

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 = Workbooks(currentGlobalReportFile).Colors

'make the lookup section into values
With ActiveSheet.Range("AM2:AX185")
Value = .Value
End With
'Selection.Copy
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
' :=False, Transpose:=False

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

'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


Thanks Again


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