refer to a cells value in a macro

[Guest]

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

 

[Bob Phillips]

You cannot have a formula and a value in the same cell. Once you paste a
value in, the formula is lost.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bernie Deitrick]

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

 

[Guest]

Sorry Bob I wasn't very clear.
I realise that cannot have a formula and a value in the same cell.

What I need the macro to do is to use the cell address generated within the
formula of cell (S27) which is ($S$28), and to paste the data there.

Thanks for your reply.

 

[Guest]

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