VBA range value from a dynamic range

Question

I'm trying to make a range value "paste" without using the copy/paste-function. I'm fairly new to VBA and don't understand why my code doesn't work. I'm sorry if this is a type of question which is already answered but I can't figure my problem out with other posts.

I'm able to select the range I want my values to copy from and paste too with

name_task = [code].select
name_task_2 = [code].select

But i can't write values to name_task_2 with

name_task_2 = name_task.value

This works, and for ME is what I've written somehow the same just more complex, but since it doesn't work it obviously isn't the same :D

sht2.Range("C2:D12") = sht1.Range("A8:B18").value 

Excel sheet1

Sub Time_Estimate()

Application.ScreenUpdating = False

Dim name_task As Variant
Dim name_task_2 As Variant

Dim R_count As Double
Dim C_count As Double

Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set sht1 = Sheet1 'Sheets("Tekla_2016")

name_task = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown))
R_count = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown)).Rows.Count
'Debug.Print name_task.Rows.Count
'Debug.Print name_task.Columns.Count
Debug.Print R_count

Set sht2 = Sheet2 'Sheets("Timeforbruk_2016 - UFERDIG")

name_task_2 = sht2.Range("C2:D2", sht2.Range("C2:D2").Offset(R_count - 1, 0))

'name_task_2 = name_task.value 'Why doesn't this work???

'sht2.Range("C2:D12") = sht1.Range("A8:B18").value 'This works but it's not dynamic


Application.ScreenUpdating = True

End Sub

EDIT: This code now work as I want:

Sub Time_Estimate()

Application.ScreenUpdating = False

Dim name_task As Range
Dim name_task_2 As Range
Dim rng_sht1 As Range
Dim rng_sht2 As Range


Dim R_count As Double
Dim C_count As Double

Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set sht1 = Sheet1 'Sheets("Tekla_2016")
Set sht2 = Sheet2 'Sheets("Timeforbruk_2016 - UFERDIG")

Set name_task = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown))
R_count = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown)).Rows.Count

Set name_task_2 = sht2.Range("C2:D2", sht2.Range("C2:D2").Offset(R_count - 1, 0))
name_task_2 = name_task.value

Application.ScreenUpdating = True

End Sub

Answer 1

someRange.Select

selects the range someRange and returns the boolean value True (you usually don't notice that because you don't try to assign it to something). That means that after

name_task = [code].select
name_task_2 = [code].select

both name_task and name_task_2 are booleans (True) and not ranges.

In the sub you posted you have another problem:

name_task = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown))

since you didn't specify what name_task is (other than Variant, which basically means nothing), vba will use the range's Value as default property and name_task will be a variant array containing sht1.Range("A8:B8", sht1.Range("A8").End(xlDown)).Value. To assign a range object you need to use Set:

Set name_task = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown))

From MSDN/this answer:

Set Keyword. In VBA, the Set keyword is necessary to distinguish between assignment of an object and assignment of the default property of the object. Since default properties are not supported in Visual Basic .NET, the Set keyword is not needed and is no longer supported.

This is actually a nice example why it is a good idea to use Option Explicit (or in your case to declare the variables properly). If you declare Dim name_task As Range VBA will tell you that something is wrong at the right line and troubleshooting becomes much easier.