Solved: Copy Worksheets from Source Workbook to another Workbook

[Slicemahn]

Hi Everyone!

I am looking to copy a set of worksheets from my source workbook to another workbook. It seems easy enough but there's a twist:

The source workbook houses pivot tables and formulas and I do not want either the formulas or pivot tables to be copied over to the new workbook.

How do I copy each worksheet in my workbook and just the values and place it another workbook.

Source Workbook: "Generator.xls"
Sheets: "Sheet1","Sheet2","Sheet3","Sheet4","Sheet5","Sheet6","pivotdata","RAW"

where "pivotdata" and "RAW" will store my pivot tables and SQL tables


New Workbook: "Report.xls"
Sheets:
"Title"

Many thanks for your help on this one.

[Bob Phillips]

Is this what you want?

[vba]

Sub ReportData()
Dim thisWB As Workbook
Dim newWB As Workbook
Dim numSheets As Long

Set thisWB = ActiveWorkbook
numSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 3 '8
Set newWB = Workbooks.Add

Call CopyData(thisWB, newWB, "Sheet1")
Call CopyData(thisWB, newWB, "Sheet2")
Call CopyData(thisWB, newWB, "Sheet3")
Call CopyData(thisWB, newWB, "Sheet4")
Call CopyData(thisWB, newWB, "Sheet5")
Call CopyData(thisWB, newWB, "Sheet6")
Call CopyData(thisWB, newWB, "pivotdata")
Call CopyData(thisWB, newWB, "RAW")

Application.SheetsInNewWorkbook = numSheets

newWB.SaveAs "Report.xls"

Set newWB = Nothing
Set thisWB = Nothing
End Sub

Private Sub CopyData(Source As Workbook, Target As Workbook, sh As String)
Static shIndex As Long
shIndex = shIndex + 1
Source.Worksheets(sh).Cells.Copy
With Target.Worksheets(shIndex)
.Cells.PasteSpecial Paste:=xlValues
.Name = sh
End With
End Sub
[/vba]