Jan Karel Pieterse wrote:

Hi Peter,

I can do this for every single cell by "opening" it, selecting its
content, and copying it. But this is very tedious when I have to do
this for many cells.

So, can you tell me whether this is an easier solution to this problem?


You can use this macro (forgot what the source is, credits to whomever
recognises this as his/her work):

Sub CopySelectionFormulae()

Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim intColCount As Integer
Dim intRowCount As Integer

'** Check that a range is selected
If Not TypeName(Selection) = "Range" Then End
'** check that the range has only one area
If Not Selection.Areas.Count = 1 Then
MsgBox "Multiple Selections Not Allowed", vbExclamation
End
End If

'** Assign selection to object variable
Set rngCopyFrom = Selection

'** This is required in case cancel is clicked.
'** Type 8 input box returns a range object if OK is
'** clicked or False if cancel is clicked.* I do not
'** know of a way to test for both cases without
'** using error trapping
On Error GoTo UserCancelled

'** Assign object variable to user-selected cell
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

'** Loop through source range assigning any formulae found
'** to the equivalent cell of the destination range.
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

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com


Hi Jan,

Thanks for the macro. Using Google Groups search I also found the
original posting of this macro. It was posted by a guy named Rob Bruce
and it appears he used to have an Excel webpage but it doesn't exist
anymore.

Anyway, the macro works pretty nicely except for one strange thing:
Whenever I select a range of cells to be copied with that macro, I
always get a copy of the cells one column to the left of the cells
that I actually selected. Knowing this I can select the cells one
column to the right of the cells that I would like to copy, but do you
have any idea what this could be due to or how to modify the macro so
that it copies the cells that I selected?

Peter