Copy a workbook, but only as value?

[George J]

I know it is possible to copy an entire workbook using worksheets.copy, but is it possible to copy all the sheets without naming them and using the

.PasteSpecial Paste:=xlValues
.PasteSpecial Paste:=xlFormats

This is so that I can copy a workbook without knowing the names of all the sheets (INCLUDING HIDDEN).

All comments welcome.

[CRWeaks23]

you could say
for x = 1 to worksheets.count
sheets(x).range("a1:IV65536").copy

next x

[CRWeaks23]

i deleted some code let me try again:

for x = 1 to worksheets.count
sheets(x).range("a1:IV65536").copy
(Insert where you want to paste the sheet here)
next x

[George J]

How could I make this copy the sheets to a new workbook? As I am pretty new to this programming lark I cannot see how to put all the copied sheets into one new workbook.

Recommendations?

[Anonymousity]

Do you want to copy the entire workbook but name the sheets as Sheet1, Sheet2, etc. instead of the names they already have?

[George J]

I got this from the microsoft news group - I wanted the sheet names copied across, but is there any way to modify this so that the page setup and Headers/footers are also transposed?
Have been trying
Worksheets(Tgt).PageSetup.RightHeader = _
Worksheets(Src).PageSetup.Rightheader
etc
but am unable to work it into the code I have. Any pointers appreciated.


Sub CopyWbkValue()
'
' ValueWbk Macro
' Macro recorded 18/03/2002 by gj
'

'

Dim MySheet As Worksheet
Dim wbNewWb As Workbook
Dim wbOldWb As Workbook
Dim shtNewSheet As Worksheet
Dim TheRange As Range

Set wbOldWb = ThisWorkbook


Dim iNumOfNewSheets As Integer
iNumOfNewSheets = Application.SheetsInNewWorkbook

With Application
.StatusBar = "Copying workbook to a new workbook..."
.ScreenUpdating = False
.SheetsInNewWorkbook = 1
Set wbNewWb = Workbooks.Add
.SheetsInNewWorkbook = iNumOfNewSheets
End With

wbNewWb.Sheets(1).Name = "DeleteMeWhenFinished"

For Each MySheet In wbOldWb.Worksheets
Set shtNewSheet = wbNewWb.Sheets.Add
shtNewSheet.Move after:=wbNewWb.Sheets(wbNewWb.Sheets.Count)
Set TheRange = MySheet.Cells

shtNewSheet.Name = MySheet.Name
TheRange.Copy

With shtNewSheet.Cells
.PasteSpecial (xlPasteValues)
.PasteSpecial (xlPasteFormats)
End With

shtNewSheet.Range("A1").Select

If Not MySheet.Visible Then
shtNewSheet.Visible = xlSheetHidden
End If

Next MySheet

wbNewWb.Sheets(wbOldWb.ActiveSheet.Index).Activate

With Application
.CutCopyMode = False
.DisplayAlerts = False
wbNewWb.Sheets("DeleteMeWhenFinished").Delete
.DisplayAlerts = True
.ScreenUpdating = True
.StatusBar = False
End With

Set MySheet = Nothing
Set wbNewWb = Nothing
Set wbOldWb = Nothing
Set shtNewSheet = Nothing
Set TheRange = Nothing


End Sub

'*****Macro by Mark Driscol

thanks

George J


[ This Message was edited by: George J on 2002-03-18 04:48 ]

[Brabantio]

You have not made it clear as to what you want to do.

[George J]

What I want to do :

If I have any spreadsheets with formula, I want to be able to create an exact copy of that spreadsheet, but with values instead of formulas (like in paste special - values).

I would like the page format to be the same as the original, the headers and footers to be pulled from the original and the page layout to be whatever the original was.

The script above creates the values part ok, but not the header/footer, margins and page layout (Portrait/Landscape).

[Brabantio]

All you have to do is use SaveAs to create a copy of your whole workbook, then select all worksheets, select all cells (click on the top left corner of the sheet), and Copy>PasteSpecial/Values.

You could record a macro or try this :-

Application.ScreenUpdating = False
With ActiveWorkbook
.Save
.SaveAs Filename:="C:Whatever.xls"
End With
Worksheets.Select
With Cells
.Copy
.PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False
[A1].Select
Worksheets(1).Select