Macro that copies formulas and pastes them in another location

[jim]

I am trying to write a macro that will copy formulas (that
consist of links to other worksheets) and paste them in
another location without increminting the cell references.
An example of the formulas is as follows:
=F:\Completed Templates\[Food 2003.xls]Food'!E417

Some of the formulas are linked to other tabs in the
workbook. Example:
=Slots!D430

I tried using the "cut" function but I can only paste it
in one location. (I need to retain the original formula.)

Any suggestions will be greatly appreciated.

Thanks,

Jim

[Jim Rech]

Convert the formulas to text, then copy them and convert them back.

Use sub A for the first part and B for the later. Select the range of
formulas first.

Sub a()
Dim Cell As Range
For Each Cell In Selection
Cell.Value = "'" & Cell.Formula
Next
End Sub

Sub b()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Cell.Formula
Next
End Sub

--
Jim Rech
Excel MVP

[Gord Dibben]

Jim

I cannot attribute this routine to its originator, because I forget where I
got it. Copy/paste to a module.

Sub CopyFormulasExact()
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim intColCount As Integer
Dim intRowCount As Integer

If Not TypeName(selection) = "Range" Then End
If Not selection.Areas.Count = 1 Then
MsgBox "Multiple Selections Not Allowed", vbExclamation
End
End If
Set rngCopyFrom = selection
On Error GoTo UserCancelled
Set rngCopyTo = Application.InputBox( _
Prompt:="Select the UPPER LEFT CELL of the " _
& "range to which you wish to paste", _
Title:="Copy Range Formulae", Type:=8).Cells(1, 1)
On Error GoTo 0
For intColCount = 1 To rngCopyFrom.Columns.Count
For intRowCount = 1 To rngCopyFrom.Rows.Count
If rngCopyFrom.Cells(intRowCount, _
intColCount).HasFormula Then
rngCopyTo.offset(intRowCount - 1, _
intColCount - 1).Formula = _
rngCopyFrom.Cells(intRowCount, _
intColCount).Formula
End If
Next intRowCount
Next intColCount

UserCancelled:
End Sub

HTH Gord Dibben Excel MVP - XL97 SR2

[jim]

Thanks for the help! This looks like it might work but I
need more clerification on the code as I am a novice at
writing code. I assume Cell refers to the cell reference
(i.e. C5). I also assume that "Range" refers to the range
of cells I want to copy (i.e. C5:P5). Is this correct?

I have 978 rows on my spreadsheet and I need to copy every
7th row up two rows. I have a macro that I intend to
manipulate that works very well for valuepasting formulas.
The code is as follows:
Sub Cpy()
'Step 1
' Copy Macro
' Macro Written 2002-01-25 by Jim Moyer
'
'
'
Dim x%
For x = 9 To 1000 Step 7
Range(Cells(x, 3), Cells((x + 1), 15)).Select
Application.CutCopyMode = False
Selection.Copy
Cells((x + 1), 3).Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Next x


End Sub

Could I manipulate your code in the same manner to
complete this task?

>.
>

[jim]

Great Code!!

Could you help me manipulate this to copy every 7th row
and paste it two rows up? I have a spreadsheet of 978 rows
that I need this code to work on.

Thanks,

Jim

>.
>