Converitng Workbook

[John Stauffer]

What would be the best way to convert a large workbook with hundreds of
array formulas, and thousands of lines of data, to a workbook with only
values in the cells?



I need to email this document to someone that is not familiar with Excel.



Thanks,



John

 

[Frank Kabel]

Hi
try:
- copy your data
- insert it with 'Edit - Paste Special - Values'

 

[John Stauffer]

That is one way, but I would lose too much formatting with the paste values
command. Also, this is 5 or 6 workbooks, and a total of about 30
worksheets.

Looking back, I should have done the project in MS Access, but Excel was
easier to work with!

John

 

[Frank Kabel]

Hi
paste the data over your existing data to save the formats. If you have
many sheets simply record a macro while doing this manually and use
this macro

 

[Fedor Shihantsov]

Run this macros for each workbook

Sub Macro1()
Dim NewBook As Workbook, CurrentBook As Workbook, Sheet As Worksheet,
NewSheet As Worksheet
Set Book = ActiveWorkbook
Set NewBook = Workbooks.Add


Application.DisplayAlerts = False
For i = 1 To NewBook.Sheets.Count - 1
NewBook.Sheets(i).Delete
Next
Application.DisplayAlerts = True

For i = 1 To Book.Sheets.Count

If i = 1 Then
Set NewSheet = NewBook.Sheets(1)
Else
Set NewSheet = NewBook.Sheets.Add
NewSheet.Move After:=NewBook.Sheets(NewBook.Sheets.Count - 1)
End If

Set Sheet = Book.Sheets(i)
Sheet.Activate
Cells.Select
Selection.Copy

NewSheet.Name = Sheet.Name
NewSheet.Activate
NewSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Next


End Sub

 

[Max]

Hi Fedor

I was interested and tried out your sub in xl97 (my ver.)

It errored at this line (believe its due to xl version):
'Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

I commented out the line and the sub then ran ok
w/o any visible impact on the column widths
in the new book.

But I'm not sure whether I'm missing out on
any new feature as a result of the commented line?
(I wouldn't want to miss out <g>)

Did notice that textboxes, autoshapes, etc
were not copied over to the new book,
which is usually required for a complete "conversion"
How could this be incorporated into your sub?

Thanks for insights

 

[Don Guillett]

Why not saveas a different file name and copy/pastevalues to each sheet.

 

[Lady Layla]

Print it as a pdf file and email it to the person


: What would be the best way to convert a large workbook with hundreds of
: array formulas, and thousands of lines of data, to a workbook with only
: values in the cells?
:
:
:
: I need to email this document to someone that is not familiar with Excel.
:
:
:
: Thanks,
:
:
:
: John
:
:

 

[Dave Peterson]

Paste:=xlPasteColumnWidths
was added in xl2k, but in xl2k, I believe that you couldn't use the constant
"xlPasteColumnWidths", you had to use:
Paste:=8

I liked Frank's suggestion to Copy|paste special|values

Option Explicit
Sub testme01()

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.UsedRange.Copy
wks.UsedRange.PasteSpecial Paste:=xlPasteValues
Next wks

End Sub

Manually, you could even select all the sheets (rightclick on any worksheet tab
and select group sheets), then ctrl-a (twice in xl2003--when you get it <vbg>.)

Edit|copy
edit|paste special values
Save as a new name--don't lose the original.

Hi Fedor

I was interested and tried out your sub in xl97 (my ver.)

It errored at this line (believe its due to xl version):
'Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

I commented out the line and the sub then ran ok
w/o any visible impact on the column widths
in the new book.

But I'm not sure whether I'm missing out on
any new feature as a result of the commented line?
(I wouldn't want to miss out <g>)

Did notice that textboxes, autoshapes, etc
were not copied over to the new book,
which is usually required for a complete "conversion"
How could this be incorporated into your sub?

Thanks for insights

 

[Max]

Thanks for the insights, Dave !
Looks the better way to go ..
Cheers

 

[John Stauffer]

Thanks for all your responses, I have several things to try.

John