Hi Kristan,
Adapting your pseudo code, try something like:
Sub Tester()
Dim srcWB As Workbook
Dim destWB As Workbook
Set srcWB = ActiveWorkbook
'copy sheet into new workbook
srcWB.Sheets("??????????").Copy
Set destWB = ActiveWorkbook
'copy the funky colours from the global report workbook
destWB.Colors = Workbooks(currentReportFile).Colors
'make the lookup section into values
With ActiveSheet.Range("AM2:AX185")
.Value = .Value
End With
destWB.Range("A1").Select
'rename sheet
ActiveSheet.Name = "????????????? " & Format(Date, "yyyymmdd")
srcWB.Sheets("SecondSheet").Copy _
After:=destWB.Sheets(destWB.Sheets.Count)
ActiveSheet.Name = "????????????? " & Format(Date, "yyyymmdd")
'break links
ActiveWorkbook.BreakLink Name:= _
"G:\????????.xls", Type:=xlExcelLinks
'save in archive folder
ReportFilename = _
"G:\?????????? " & _
Format(Date, "yyyymmdd") & ".xls"
destWB.SaveAs Filename:=ReportFilename
'close file
destWB.Close
End Sub
'<<===========================
---
Regards,
Norman
"Kstalker" wrote in
message ...
Hello.
I have the following sub doing exactly what I need but have just had a
request to pull two sheets from the same workbook and save them both in
the same newly created workbook.
'copy sheet into new workbook
Sheets("??????????").Copy
'copy the funky colours from the global report workbook
ActiveWorkbook.Colors = Workbooks(currentReportFile).Colors
'make the lookup section into values
Range("AM2:AX185").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
'rename sheet
Sheets("???????????").Name = "????????????? " & Format(Date,
"yyyymmdd")
'break links
ActiveWorkbook.BreakLink Name:= _
"G:\????????.xls", Type:=xlExcelLinks
'save in archive folder
ReportFilename = _
"G:\?????????? " & _
Format(Date, "yyyymmdd") & ".xls"
ActiveWorkbook.SaveAs Filename:=ReportFilename
'close file
ActiveWorkbook.Close
End Sub
I am stumped as to how to copy both sheets across at tha same time and
put them in the same workbook.
All help greatly appreciated.
Kristan
--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile:
http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=466141