Copy cells and paste to an address given in another cell (VBA Newbie)

[WightRob]

Can anyone help with this problem. I am trying write a macro to copy a range of cells on one worksheet, then goto an address which is specified in a cell. (This address is dynamic and changes according to the date) The data can then be copied to the new location. I am attaching a bitmap image of my example.

Here are my required actions:
Go to cell B27-B33 on worksheet "Entry Sheet"
Copy cells B27-B33
Go To the address in cell B20 (in this case Data!J29)
Paste the copied cell

Thanks in anticipation

Rob

[Rowan Drummond]

Maybe like this:

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

Hope this helps
Rowan

WightRob wrote:
> Can anyone help with this problem. I am trying write a macro to copy a
> range of cells on one worksheet, then goto an address which is
> specified in a cell. (This address is dynamic and changes according to
> the date) The data can then be copied to the new location. I am
> attaching a bitmap image of my example.
>
> Here are my required actions:
> Go to cell B27-B33 on worksheet "Entry Sheet"
> Copy cells B27-B33
> Go To the address in cell B20 (in this case Data!J29)
> Paste the copied cell
>
> Thanks in anticipation
>
> Rob
>
>
> +-------------------------------------------------------------------+
> |Filename: DataCopyExample.doc |
> |Download: http://www.excelforum.com/attachment.php?postid=3928 |
> +-------------------------------------------------------------------+
>

[WightRob]

Thanks Rowan,

That did the trick.

Rob

[WightRob]

As I said in my last post, the problem was solved. Unfortunately that was only with my simplfied example.

In my actual workbook, the values in the range B27:B33 are fed in from a calculator sheet. Rowan's code is copying the actual cell data, which is now setting up a link to the calculator sheet. What I need is a "Paste Special" = Value, rather that a simple "Paste", is this possible?

I am new to VBA coding, so all help is appreciated.

Rob

[Rowan Drummond]

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

WightRob wrote:
> As I said in my last post, the problem was solved. Unfortunately that
> was only with my simplfied example.
>
> In my actual workbook, the values in the range B27:B33 are fed in from
> a calculator sheet. Rowan's code is copying the actual cell data,
> which is now setting up a link to the calculator sheet. What I need is
> a "Paste Special" = Value, rather that a simple "Paste", is this
> possible?
>
> I am new to VBA coding, so all help is appreciated.
>
> Rob
>
>