Paste special: formulas

[Fred Davis]

Am I correct in thinking that it should be possible to
copy only the formulas from one sheet to another?

I want to set up a second sheet based on the model of the
first sheet, so all I want to do is copy the formulas not
the entered values.

Copy|Paste Special-Formulas doesn't seem to do this.

Any comments, please.

Fred

 

[Doug Kanter]

1) When you say "doesn't seem to do this", what exactly happens or does not
happen?

2) The cells in the target sheet - are they in the exact same place as the
ones where the formulae are being copied from in the source sheet?

3) Are you using named ranges, or just cell coordinates in your formulae?

4) Could you simplify this by getting the first sheet just right, saving it,
and then doing a "Save As" to create a duplicate with a new name?

 

[Guest]

Doug,

My procedure:
Highlight whole of first sheet|Copy|switch to second sheet
A1|Paste Special:Formulas. Result: exact copy of first
sheet - entries and formulas.

All I want to copy is the model formulas to the second
sheet.

Fred

 

[Frank Kabel]

Hi
try the following:
- first copy your formulas as shown in your description
- after this select your target and hit F5
- choose 'Special' and select 'Constants'
- hit 'DEL'

 

[Fred Davis]

Frank,

Still doesn't work for me!

Fred

-----Original Message-----
Hi
try the following:
- first copy your formulas as shown in your description
- after this select your target and hit F5
- choose 'Special' and select 'Constants'
- hit 'DEL'

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[David McRitchie]

Hi Fred,
What happened instead of working.

Let's start over, copying the sheet
will copy everything, formulas, constants, formatting,
macro code associated with original sheet (i.e. Sheet Event macros).

-- Edit, Move or Copy Sheet, be sure to check [x] make a copy
-- Ctrl+A (you're now on the new sheet) to select all cells
-- Ctrl+G (F5 *or* Edit, GoTo), Special, Constants *and* each of
Numbers, Text, Logicals, and Errors

Copying the entire sheet is easier. In fact I'm not
sure if versions prior to Excel 2000 allow you to copy
formats alone. Also by copying the entire sheet
you don't miss anything such as Worksheet Event macros.

 

[Fred Davis]

David,

What does Paste Special:Formulas do?

I have a large spreadsheet that I want to duplicate.
However, because of the complexity of it I only want to
duplicate the formulas to the new sheet and not have to
delete the entries I have made in the original sheet.

I thought Copy|Paste Special:Formulas would do this for
me. But, when I highlight the whole of the first sheet
and try Copy|Paste Special:Formulas the whole of the sheet
is copied and I still will have to delete the entries
(which are all over the place) that were made in the
original. I can do this - but I go back to my question:
what does Paste Special:Formulas do?

Regards,
Fred

-----Original Message-----
Hi Fred,
What happened instead of working.

Let's start over, copying the sheet
will copy everything, formulas, constants, formatting,
macro code associated with original sheet (i.e. Sheet Event macros).

-- Edit, Move or Copy Sheet, be sure to check [x] make a copy
-- Ctrl+A (you're now on the new sheet) to select all cells
-- Ctrl+G (F5 *or* Edit, GoTo), Special, Constants *and* each of
Numbers, Text, Logicals, and Errors

Copying the entire sheet is easier. In fact I'm not
sure if versions prior to Excel 2000 allow you to copy
formats alone. Also by copying the entire sheet
you don't miss anything such as Worksheet Event macros.

Still doesn't work for me!

Frank Kabel

.

 

[David McRitchie]

Hi Fred,

Thanks for explaining, and I see your dilemma in trying to
copy all of the formulas after selecting them (F5, Formulas (+all forms))
-- "That command cannot be used with multiple selections".

So here is all you have to do: (the paste special will run almost instantly)
Select your master sheet
Ctrl+A selects all cells
Select your sheet to receive formulas
Edit, Paste, special, formulas (each part under)

"Paste Special Formulas" would only paste the formulas.

should be able to be done in a quick macro

Sub Macro33()
Sheets("master").Cells.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlFormulas, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

However both of the above suffer from a limitation
http://support.microsoft.com/?kbid=832293
problem so you will probably have to use the horrendous
macro below:

Could combine the two methods but I ran out of time now.

----------------------------------------------------------------------------
Forget about the rest of this, and exercise in futility:
(wrong)So you will have to do them one range at a time.
Probably you can copy whole columns of formulas.

Another way would be to write a macro and to go through
the collection of formulas on the master sheet and
copy them one by one to the sheet needing the new formulas.

You might want to put something into the macro to make
sure you are not updating incorrect worksheet or cells
so as not to destroy valuable data.

If you want to expand this to several sheets you can use
an array of sheets in a macro of weed them out by going
through all of them see collections.htm on my site.

Try this on a copy of your wookbook. Coloring optional.
If macro works as desired turn off screen updating and
calculation as seen in slowresp.htm on my site.

Sub CopyFormulasFromMaster()
Dim Rng As Range, i As Long, cell As Range, area As Range
Dim msg As String
If UCase(Left(ActiveSheet.Name, 5)) <> "SLAVE" Then
MsgBox "aborting not authorized from this sheet"
Exit Sub
End If
On Error Resume Next
Set Rng = Sheets("master").Cells.SpecialCells(xlFormulas)
If Err.Number <> 0 Then
MsgBox "No ""Master"" worksheet to copy from, or no formulas"
Exit Sub
End If
If Rng.Areas.Count = 1 Then
'more than 8192 looks like 1
Set Rng = Sheets("master").UsedRange
MsgBox = "have to use usedrange instead of formulas"
' http://support.microsoft.com/?kbid=832293
End If
MsgBox Rng.Count & " formulas allow " & _
Int(Rng.Count / 260) & " seconds"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Rng.Interior.ColorIndex = 38 'optional
For Each cell In Rng
If cell.HasFormula Then 'test because of MSKB 832293
Range(cell.Address).Formula = cell.Formula
End If
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


If not familiar with macros -- To install and use a macro see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Explanation of coding used can be found in
proper.htm for use of special cells and turning off calc,
and inability to have more than 8192 separate areas.
color.htm for coloring -- optional marking within master

In answer to your question "Paste Special Formulas" would
only paste the formulas.

David,

What does Paste Special:Formulas do?

I have a large spreadsheet that I want to duplicate.
However, because of the complexity of it I only want to
duplicate the formulas to the new sheet and not have to
delete the entries I have made in the original sheet.

I thought Copy|Paste Special:Formulas would do this for
me. But, when I highlight the whole of the first sheet
and try Copy|Paste Special:Formulas the whole of the sheet
is copied and I still will have to delete the entries
(which are all over the place) that were made in the
original. I can do this - but I go back to my question:
what does Paste Special:Formulas do?

Regards,
Fred

-----Original Message-----
Hi Fred,
What happened instead of working.

Let's start over, copying the sheet
will copy everything, formulas, constants, formatting,
macro code associated with original sheet (i.e. Sheet Event macros).

-- Edit, Move or Copy Sheet, be sure to check [x] make a copy
-- Ctrl+A (you're now on the new sheet) to select all cells
-- Ctrl+G (F5 *or* Edit, GoTo), Special, Constants *and* each of
Numbers, Text, Logicals, and Errors

Copying the entire sheet is easier. In fact I'm not
sure if versions prior to Excel 2000 allow you to copy
formats alone. Also by copying the entire sheet
you don't miss anything such as Worksheet Event macros.

Still doesn't work for me!

Frank Kabel
try the following:
- first copy your formulas as shown in your description
- after this select your target and hit F5
- choose 'Special' and select 'Constants'
- hit 'DEL'

Fred Davis wrote:
Am I correct in thinking that it should be possible to
copy only the formulas from one sheet to another?

I want to set up a second sheet based on the model of the
first sheet, so all I want to do is copy the formulas not
the entered values.

Copy|Paste Special-Formulas doesn't seem to do this.

.

 

[David McRitchie]

Hi Fred,
Corrections, the short macro (macro33) was invalid
a new macro is supplied below faster than the longer
one previously included.

This is what copy paste formulas does:
Copy, special formulas copies what is seen
on the formula bar (both formulas and constants) as
opposed to values (both formulas and constants).

So this new version of the macro processes
64 rows at a time if there are more than 8192 ranges
(since this can't be tested, the actual test will be to
test for more than 1 areas in the range).
http://support.microsoft.com/?kbid=832293

Option Explicit
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Sub CopyFormulasFromMaster()
'D.McRitchie, excel.misc, 2004-05-20 revised for speed
' For the sake of speed Copy Range has been used
' but it will be copying both formulas and formatting
' for those cells that have formulas.
Dim Rng As Range, i As Long, cell As Range, area As Range
Dim msg As String, x As Long, j As Long
Dim masterN As String, msgDescr As String
masterN = InputBox("Supply name of 'Master' worksheet", _
"Get Formulas from Master", "Master")
If masterN = "" Then
MsgBox "exiting for by your command, no master sheetname supplied"
Exit Sub
End If
'quick attempt to make sure lastcell position is correct in master
x = Sheets(masterN).UsedRange.Rows.Count
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim myTime(4) As Double, formulasCnt As Long, areasCnt As Long
myTime(1) = timeGetTime()
Dim subArea As Range
Set Rng = Sheets(masterN).Cells.SpecialCells(xlFormulas)
On Error GoTo 0
If Rng.Areas.Count > 1 Then 'okay for fast track
For j = 1 To Rng.Areas.Count
'Warning: -- COPY will copy formula, and formatting
Rng.Areas.Item(j).Copy _
Destination:=ActiveSheet.Range(Rng.Areas.Item(j).Address)
Next j
areasCnt = Rng.Areas.Count
formulasCnt = Rng.Count 'everything in range is a formula
msgDescr = "Fast Track was used on " & Rng.Count _
& " formulas, in " & Rng.Areas.Count & " areas"
GoTo done
End If
msgDescr = "More than 8192 formula areas -- slow track was used"
On Error Resume Next
For i = 1 To x Step 64 'and use that count for the loop
Err.Number = 0
Set Rng = Sheets(masterN).Rows(i & ":" & i + 63).SpecialCells(xlFormulas)
If Err.Number <> 0 Then GoTo next_i
For j = 1 To Rng.Areas.Count
'Warning: -- COPY will copy formula, and formatting
Rng.Areas.Item(j).Copy _
Destination:=ActiveSheet.Range(Rng.Areas.Item(j).Address)
'Rng.Areas.Item(j).Interior.ColorIndex = 39
Next j
formulasCnt = formulasCnt + Rng.Count
areasCnt = areasCnt + Rng.Areas.Count
' '-- if you don't want to copy formatting don't use areas
' '-- but it may take a lot longer
' For Each cell In Rng
' Range(cell.Address).Formula = cell.Formula
' formulasCnt = formulasCnt + 1
' Next cell
next_i:
Next i
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
myTime(2) = timeGetTime()
myTime(3) = (myTime(2) - myTime(1)) / 1000
myTime(4) = x / myTime(3)
MsgBox msgDescr & Chr(10) _
& areasCnt & " Formula areas" & Chr(10) _
& Format(myTime(3), "#.000") _
& " seconds, for " & x & " rows, at " _
& Format(myTime(4), "#.0") & " rows per second" _
& Chr(10) & formulasCnt & " formulas at " & _
Format(formulasCnt / myTime(3), "0.0") & " per second"
End Sub


My results from testing on a 600mHz machine Excel 2000
Fast Track was used on 30170 formulas, in 10 areas
112.522 seconds, for 3017 rows, at 26.8 rows per second
30170 formulas at 268.1 per second

Fast Track was used on 6034 formulas, in 2 areas
22.232 seconds, for 3017 rows, at 135.7 rows per second
6034 formulas at 271.4 per second

More than 8192 formula areas -- slow track was used
13628 Formula areas
88.006 seconds, for 3017 rows, at 34.3 rows per second
29264 formulas at 332.5 per second

More than 8192 formula areas -- slow track was used
13629 Formula areas
31.826 seconds, for 3017 rows, at 94.8 rows per second
29263 formulas at 919.5 per second














So it really is going to be how many formulas you
have to copy, whether you have 5 areas,
or 13578 areas. If I did 64 rows at a time
(8192 / 256 * 2) the results were still about the same.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[David McRitchie]

Hi Fred,
Changed the macro to use the clipboard, so it will use
Paste Special Formulas since each formula area only
contains formulas -- that way can avoid copying the
formatting, just the formulas (formulas only, no constants).

The first timing in the previous post was incorrect since it
did not match the macro shown. New timings have been
included for this macro below it.

Option Explicit
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Sub CopyFormulasFromMaster()
'D.McRitchie, excel.misc, 2004-05-20 revised for speed
' For the sake of speed Copy Range has been used
' but it will be copying both formulas and formatting
' for those cells that have formulas.
Dim Rng As Range, i As Long, cell As Range, area As Range
Dim msg As String, x As Long, j As Long
Dim masterN As String, msgDescr As String
masterN = InputBox("Supply name of 'Master' worksheet", _
"Get Formulas from Master", "Master")
If masterN = "" Then
MsgBox "exiting for by your command, no master sheetname supplied"
Exit Sub
End If
'quick attempt to make sure lastcell position is correct in master
x = Sheets(masterN).UsedRange.Rows.Count
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim myTime(4) As Double, formulasCnt As Long, areasCnt As Long
myTime(1) = timeGetTime()
Dim subArea As Range
Set Rng = Sheets(masterN).Cells.SpecialCells(xlFormulas)
On Error GoTo 0
If Rng.Areas.Count > 1 Then 'okay for fast track
For j = 1 To Rng.Areas.Count
'Warning: -- COPY will copy formula, and formatting
Rng.Areas.Item(j).Copy
Range(Rng.Areas.Item(j).Address).PasteSpecial _
Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next j
areasCnt = Rng.Areas.Count
formulasCnt = Rng.Count 'everything in range is a formula
msgDescr = "Fast Track was used on " & Rng.Count _
& " formulas, in " & Rng.Areas.Count & " areas"
GoTo done
End If
msgDescr = "More than 8192 formula areas -- slow track was used"
On Error Resume Next
For i = 1 To x Step 64 'and use that count for the loop
Err.Number = 0
Set Rng = Sheets(masterN).Rows(i & ":" & i + 63).SpecialCells(xlFormulas)
If Err.Number <> 0 Then GoTo next_i
For j = 1 To Rng.Areas.Count
'Warning: -- COPY will copy formula, and formatting
' Rng.Areas.Item(j).Copy _
' Destination:=ActiveSheet.Range(Rng.Areas.Item(j).Address)

Rng.Areas.Item(j).Copy
Range(Rng.Areas.Item(j).Address).PasteSpecial _
Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'Rng.Areas.Item(j).Interior.ColorIndex = 39
Next j
formulasCnt = formulasCnt + Rng.Count
areasCnt = areasCnt + Rng.Areas.Count
' '-- if you don't want to copy formatting don't use areas
' '-- but it may take a lot longer
' For Each cell In Rng
' Range(cell.Address).Formula = cell.Formula
' formulasCnt = formulasCnt + 1
' Next cell
next_i:
Next i
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
myTime(2) = timeGetTime()
myTime(3) = (myTime(2) - myTime(1)) / 1000
myTime(4) = x / myTime(3)
MsgBox msgDescr & Chr(10) _
& areasCnt & " Formula areas" & Chr(10) _
& Format(myTime(3), "#.000") _
& " seconds, for " & x & " rows, at " _
& Format(myTime(4), "#.0") & " rows per second" _
& Chr(10) & formulasCnt & " formulas at " & _
Format(formulasCnt / myTime(3), "0.0") & " per second"
End Sub

There may really be fewer formula areas on fast track
but since I look for them 64 rows at a time there will
be several more additional areas for each 64 rows.

Fast Track was used on 41182 formulas, in 1565 areas
1565 Formula areas
12.017 seconds, for 3017 rows, at 251.1 rows per second
41182 formulas at 3427.0 per second

Fast Track was used on 25553 formulas, in 1486 areas
1486 Formula areas
13.619 seconds, for 3017 rows, at 221.5 rows per second
25553 formulas at 1876.3 per second

More than 8192 formula areas -- slow track was used
13629 Formula areas
71.403 seconds, for 3017 rows, at 42.3 rows per second
29263 formulas at 409.8 per second