Simple yet...Copy formatting (including column/row dimensions) and values but not formulas
 

This may sound nuts but I cannot find a thread that states how to
select an entire sheet and copy it to a second work book but without
formulas.
Goal?
I have a sheet with hundreds of array formulas that is simply a report
that needn't be dynamically updated over the course of a day.
I want this sheet to make a "values" copy of itself everytime it
launches.
Then I can use it as source without massive recalcs slowing things
down.

So far I have:
Application.Calculation = xlCalculationManual
' Range("A1").CurrentRegion.Copy --only copies A1, no good
' Range("A1:IV9999").Copy --copies that whole range but when formats
paste it does cell formatting but the widths and heights of cols/rows
are not transmitted.
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveWorkbook.SaveAs ("ALL SALES VALUES")

A way to mimic ctr-A,ctr-A (select ALL) in vba is what I seem to
missing.

Any help would be great.
thanks

Simple yet...Copy formatting (including column/row dimensions) and values but not formulas
 

the only way i've been able to do this is to copy & paste it once, with
all formats & formulas, and then paste a 2nd time, with values only.
in a macro, i wouldn't think this would add tons of time - you don't
have to re-select or re-copy the data, just paste, then
pastespecial:=xlvalues.
hope it helps! somebody else may have a better idea.
susan


Finny wrote:
This may sound nuts but I cannot find a thread that states how to
select an entire sheet and copy it to a second work book but without
formulas.
Goal?
I have a sheet with hundreds of array formulas that is simply a report
that needn't be dynamically updated over the course of a day.
I want this sheet to make a "values" copy of itself everytime it
launches.
Then I can use it as source without massive recalcs slowing things
down.

So far I have:
Application.Calculation = xlCalculationManual
' Range("A1").CurrentRegion.Copy --only copies A1, no good
' Range("A1:IV9999").Copy --copies that whole range but when formats
paste it does cell formatting but the widths and heights of cols/rows
are not transmitted.
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveWorkbook.SaveAs ("ALL SALES VALUES")

A way to mimic ctr-A,ctr-A (select ALL) in vba is what I seem to
missing.

Any help would be great.
thanks