Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy an excel sheet from one file to another including formulas | Excel Discussion (Misc queries) | |||
Copy (Formulas and Formatting ONLY) no values to other rows | Excel Discussion (Misc queries) | |||
Office XP - Excel 2002; Column formatting & Formulas Do Not Copy | Excel Worksheet Functions | |||
Copy and paste values, formatting and formulas | Excel Discussion (Misc queries) | |||
selectin all the values in a column including null values | Excel Programming |