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

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

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

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

Norman


Neither declared nor dimmed.... which I imagine is the issue. Am unsur
how to add.

The error is "runtime error 438 object dosen't support thi
property or method"

Krista

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

Hi Kristan,

(1) Which code line is highlighted when the error occurs?

(2) What value have you assigned to: 'currentReportFile'?

BTW, I would stronly suggest that you always put the statement:

Option Explicit

at the head of each module. Better still, in the VBE:

Tools | Options | Editor Tab | Check 'Require Variable Declaration

This will automatically insert the Option Explicit declaration into each
module.

For a detailed rationale of this suggestion, see Chip Pearson's:
' Using Variables (Properly) In VBA'

http://www.cpearson.com/excel/variables.htm


---
Regards,
Norman



"Kstalker" wrote in
message ...

Norman


Neither declared nor dimmed.... which I imagine is the issue. Am unsure
how to add.

The error is "runtime error 438 object dosen't support this
property or method"

Kristan


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

Hi Kristan,

Change:

destWB.Range("A1").Select

to

Activesheet.Range("A1").Select

---
Regards,
Norman



"Kstalker" wrote in
message ...

Norman


Neither declared nor dimmed.... which I imagine is the issue. Am unsure
how to add.

The error is "runtime error 438 object dosen't support this
property or method"

Kristan


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