Copy two sheets

  • Thread starter Thread starter Kstalker
  • Start date Start date
K

Kstalker

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
 
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
'<<===========================
 
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").Copy

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
 
Hi Kristan,
Have adapted etc and can't seem to get past the "Set destWB =
ActiveWorkbook".
Click to expand...

(1) What error are you getting and what is highlighted when the error
occurs?
(2) Where is 'currentGlobalReportFile' declared and how is it dimmed?
 
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
 
Back
Top