Getting a range from a cell value in a simple copy action

Nachtschade

New Member
Joined
Dec 6, 2013
Messages
30
Hi there,

I'm sure this is possible. I am trying to copy the values of range k31:k54 to another range (x). Range x changes and is noted in cell s66. Currently s66 has
cr2:cr25

<tbody>
</tbody>
as it's value.

So the vba action needs to copy everything from k31:k54 to cr2:cr25.

I've tried this:

Code:
Sub actie()
Dim val As String
val = Range("s66").Value
Range.val = Range("k31:aw54").Value
Application.Calculate
End Sub

I'm sure I'm (somewhat) close but I don't know what I should change. Can anyone shed a light?
 
Last edited:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Try this


Code:
Sub actie()
    Dim wVal As String
    wVal = Range("S66").Value
    Range(wVal).Value = Range("K31:K54").Value
End Sub

Notes:
val is a reserved word in VBA, is the name of the function Val, the correct thing is to use any other data, for example wVal.

In cell S66 you must have this:
cr2:cr25
 
Upvote 0
Try this


Code:
Sub actie()
    Dim wVal As String
    wVal = Range("S66").Value
    Range(wVal).Value = Range("K31:K54").Value
End Sub

Notes:
val is a reserved word in VBA, is the name of the function Val, the correct thing is to use any other data, for example wVal.

In cell S66 you must have this:
cr2:cr25

Yeah that works. Thanks a lot. So I actually did need parenthesis for that.

As for the val thing; also thanks for that info. I got that from another piece of code that I googled to try and solve this.
 
Upvote 0
Your VBA using AW54 doesn't match your explanation of wanting the data from K31:K54 to the range specified in S66.

Try this, based on the explanation rather than the sample VBA:

Code:
Sub MoveValues()
Dim zval As String
zval = Range("S66")
Range(zval) = Range("K31:K54").Value
End Sub
 
Last edited:
Upvote 0
Yeah that works. Thanks a lot. So I actually did need parenthesis for that.

As for the val thing; also thanks for that info. I got that from another piece of code that I googled to try and solve this.

I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top