Thank you very much Bernie it works like a dream!!!

"Bernie Deitrick" wrote:

loulou,

Replace:

Range("S27").Select
Selection.PasteSpecial PASTE:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

with

Range(Range("S27").Value).PasteSpecial PASTE:=xlValues

HTH,
Bernie
MS Excel MVP

"loulou" wrote in message
...
Is it possible to refer to the cell address generated within a cell to
specify a location to paste.

The macro below pastes all the data to cell (S27). I need the data to be
pasted to the Value in cell (S27) not the cell itself. The formula already
in
this cell generates a cell address, currently ($s$28) and this will be
changed by the user each time the macro is ran.

Do I need to enter the word value somewhere???

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 24.02.2005
'

'
Range("C20:O22").Select
Selection.COPY
Range("S27").Select
Selection.PasteSpecial PASTE:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("N11").Select
Application.CutCopyMode = False
End Sub