Copy a workbook, but only as value?

George J

Well-known Member
Joined
Feb 15, 2002
Messages
959
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. :cool:
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
you could say
for x = 1 to worksheets.count
sheets(x).range("a1:IV65536").copy
<insert wherever you want to paste it>
next x
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Do you want to copy the entire workbook but name the sheets as Sheet1, Sheet2, etc. instead of the names they already have?
 
Upvote 0
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 :biggrin:
This message was edited by George J on 2002-03-18 04:48
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top