Pasting to an address specified in a cell
 

I am trying to sort out a macro that will copy a range of cells, go t
an address that is specified in another cell and paste the data. Th
address changes relative to the current date.

I have been give a solution that worked with my simplified example a
shown in the attached Word file. The problem is that the data in cell
B27:B33 on the worksheet "Entry Sheet" in my actual working workbook ar
the result of calculations from another sheet and not simply numerica
data. The solution I have been given is pasting the cells data, i.e
the formulae, rather than the values. Does anyone have any suggestion
how this macro may be changed to work as a "Paste Special + Value
rather than just "Paste"?

The original solution ftrom Rowan Drummond:

Sub CopyIt()
Dim CTo As Range
Dim CRng As String
CRng = Sheets("Entry Sheet").Range("B20").Value
Set CTo = Sheets(Left(CRng, InStr(1, CRng, "!") - 1)). _
Range(Right(CRng, Len(CRng) - InStr(1, CRng, "!")))
Sheets("Entry Sheet").Range("B27:B33").Copy CTo
End Su

+-------------------------------------------------------------------
|Filename: DataCopyExample.doc
|Download: http://www.excelforum.com/attachment.php?postid=3940
+-------------------------------------------------------------------

--
WightRo
-----------------------------------------------------------------------
WightRob's Profile: http://www.excelforum.com/member.php...fo&userid=1379
View this thread: http://www.excelforum.com/showthread.php?threadid=47862

Pasting to an address specified in a cell
 

Hi Rob

Try:

Sub CopyIt()
Dim CTo As Range
Dim CRng As String
CRng = Sheets("Entry Sheet").Range("B20").Value
Set CTo = Sheets(Left(CRng, InStr(1, CRng, "!") - 1)). _
Range(Right(CRng, Len(CRng) - InStr(1, CRng, "!")))
Sheets("Entry Sheet").Range("B27:B33").Copy
CTo.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Regards
Rowan

PS Replied in original thread about half an hour ago.

WightRob wrote:
I am trying to sort out a macro that will copy a range of cells, go to
an address that is specified in another cell and paste the data. The
address changes relative to the current date.

I have been give a solution that worked with my simplified example as
shown in the attached Word file. The problem is that the data in cells
B27:B33 on the worksheet "Entry Sheet" in my actual working workbook are
the result of calculations from another sheet and not simply numerical
data. The solution I have been given is pasting the cells data, i.e.
the formulae, rather than the values. Does anyone have any suggestions
how this macro may be changed to work as a "Paste Special + Value"
rather than just "Paste"?

The original solution ftrom Rowan Drummond:

Sub CopyIt()
Dim CTo As Range
Dim CRng As String
CRng = Sheets("Entry Sheet").Range("B20").Value
Set CTo = Sheets(Left(CRng, InStr(1, CRng, "!") - 1)). _
Range(Right(CRng, Len(CRng) - InStr(1, CRng, "!")))
Sheets("Entry Sheet").Range("B27:B33").Copy CTo
End Sub


+-------------------------------------------------------------------+
|Filename: DataCopyExample.doc |
|Download: http://www.excelforum.com/attachment.php?postid=3940 |
+-------------------------------------------------------------------+

Pasting to an address specified in a cell
 

Sub CopyIt()
Dim CTo As Range
Dim CRng As String
CRng = Sheets("Entry Sheet").Range("B20").Value
Set CTo = Sheets(Left(CRng, InStr(1, CRng, "!") - 1)). _
Range(Right(CRng, Len(CRng) - InStr(1, CRng, "!")))
Sheets("Entry Sheet").Range("B27:B33").Copy
CTo.PasteSpecial xlPasteValues
End Sub

--
Regards,
Tom Ogilvy



"WightRob" wrote in
message ...

I am trying to sort out a macro that will copy a range of cells, go to
an address that is specified in another cell and paste the data. The
address changes relative to the current date.

I have been give a solution that worked with my simplified example as
shown in the attached Word file. The problem is that the data in cells
B27:B33 on the worksheet "Entry Sheet" in my actual working workbook are
the result of calculations from another sheet and not simply numerical
data. The solution I have been given is pasting the cells data, i.e.
the formulae, rather than the values. Does anyone have any suggestions
how this macro may be changed to work as a "Paste Special + Value"
rather than just "Paste"?

The original solution ftrom Rowan Drummond:

Sub CopyIt()
Dim CTo As Range
Dim CRng As String
CRng = Sheets("Entry Sheet").Range("B20").Value
Set CTo = Sheets(Left(CRng, InStr(1, CRng, "!") - 1)). _
Range(Right(CRng, Len(CRng) - InStr(1, CRng, "!")))
Sheets("Entry Sheet").Range("B27:B33").Copy CTo
End Sub


+-------------------------------------------------------------------+
|Filename: DataCopyExample.doc |
|Download: http://www.excelforum.com/attachment.php?postid=3940 |
+-------------------------------------------------------------------+

--
WightRob
------------------------------------------------------------------------
WightRob's Profile:
http://www.excelforum.com/member.php...o&userid=13799
View this thread: http://www.excelforum.com/showthread...hreadid=478624

Pasting to an address specified in a cell
 

Thanks Rowan & thanks Tom,

Thats' sorted it now and helped me to understand VBA programming a
little more.

Can anyone suggest any good sites for VBA tutorials?

Regards

Rob

:) :) :)


--
WightRob
------------------------------------------------------------------------
WightRob's Profile: http://www.excelforum.com/member.php...o&userid=13799
View this thread: http://www.excelforum.com/showthread...hreadid=478624

Pasting to an address specified in a cell
 

You're welcome Rob.

Try these links from David McRitchie for getting started with VBA.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel...m#vbatutorials

Regards
Rowan

WightRob wrote:
Thanks Rowan & thanks Tom,

Thats' sorted it now and helped me to understand VBA programming a
little more.

Can anyone suggest any good sites for VBA tutorials?

Regards

Rob

:) :) :)