Copy two sheets

[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

 

[Norman Jones]

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
'<<===========================

 

[Kstalker]

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

 

[Norman Jones]

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?

 

[Kstalker]

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

 

[Norman Jones]

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

 

[Norman Jones]

Hi Kristan,

Change:

destWB.Range("A1").Select

to

Activesheet.Range("A1").Select

 

[Kstalker]

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

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

 

[Norman Jones]

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

 

[Kstalker]

Morning Norman

That replacement works, but I still am not capturing the values in th
new Site Stats sheet just "#NAME?" for each entry. Hmmmm, I have trie
rearranging sequence etc but keep arriving at the same conclusion
which is that the values are being pasted before the link to othe
data locations within the source workbook is cut....

Will continue reading through forums, any advice greatly appreciated.

Cheers

Kristan

Public Sub testing()

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 = srcWB.Colors

'make the lookup section into values
With ActiveSheet.Range("AM2:AX185")
.Value = .Value
End With

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

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

'break links
ActiveWorkbook.BreakLink Name:= _
"G:\ch\shared04\queue stats\Kristan Stalker\Global Report\Global Repor
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 Su

 

[Norman Jones]

Hi Kristan,

I could not replicate your experience.

I note that you speak of the new Site Stats sheet. What about the new
CSB_Daily_Summary sheet, which is the first of the two sheets in the new
workbook? Are values correctly shown in this first sheet?

Both sheets in the new workbook are created in analagous manner: an entire
worksheet is copied and then the copied data is converted to values.

Given this, why do you need to break links? Data converted to its values
equvalent has no links.

It would appear that I am missing something fundamental to your situation.

For testing purposes, I set up a source book with a Daily Summary sheet
and a Site Stat sheet. Both sheets included formulae and links to a another
workbook.

I ran the code in this test environment without your observed problems. I
excluded the break links code line because of Excel version problems (my
test machine was set up for xl2k) and because I deemed this unnecessary -
see my links comments earlier.

 

[Kstalker]

Thanks in advance for your thoughts Norman.

After looking into the areas you have highlighted I have one area tha
may be causing the issue.

The CSB_Daily_Summary sheet has copied no problem as has the Site Stat
excluding the values. The values are sourced form formulas and othe
sheets for the CSB_Daily_Summary sheet wheras they are sourced directl
from a seperate pivot table in the workbook for the Site Stats sheet.

Would this prevent the values copying? I have noticed that the initia
copy is not brining values across just the "NAME#" error with th
formula embedded.

Re the breaklink, I ran the code minus the breaklink and still the sam
outcome, although the size of the new workbook was larger. So this doe
not seem to be the issue

 

[Norman Jones]

Hi Kristan

Try replacing:

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

with:

With ActiveSheet
.Name = "Site Stats" & Format(Date, "yyyymmdd")
.UsedRange.Copy
.UsedRange.PasteSpecial Paste:=xlValues
End With

 

[Kstalker]

Thanks Norman.

I have found the root of the problem.... I have a public function
running on every cell in the site stats sheet correcting error fields.


"Public Function derror(v_input As Variant) As Variant
If Application.WorksheetFunction.IsError(v_input) = True Then
derror = 0
Else
derror = v_input
End If
End Function"

When removed the data and values copy correctly.

Thanks for your help.

Kristan