Set one range to the value of another range on another sheet

[szita2000]

Hi guys.
I am trying to replace a copy/paste special with setting the value of a range to the values of another, but I can't work it out.
I receive no error message, but the values not getting written in to my rgto range

This is where I am at the moment: The last few lines are the ones not working.

Just thinking out loud. But ...
Is my problem that the valules are not loaded in to an array with the () operator?
Wonder if that is the solution to say the rngto = arr().

Sub ArchiveThisLine()
    
    Dim b As Range
    Dim lrto As Long, rgSource As Range, rgTo As Range
    
    Application.EnableEvents = False
    
    lrto = Sheet25.Range("A" & Rows.Count).End(xlUp).Row
    
    
    'Getting the Cell
    Set b = ActiveSheet.Buttons(Application.Caller).TopLeftCell
    
    'Set Cell value to nothing (There is a 1 placed there so we won't make hundreds of buttons if there are a button there already
    b.Value = ""
    
    
    'Copy the Name, Gépegység, Veszteség (Because they merging cells. - Bastards. )
    Set rgSource = Sheet23.Range(Cells(b.Row, 2), Cells(b.Row, 4))
    Set rgTo = Sheet25.Range("A" & lrto + 1)
    
    On Error GoTo CustLabel
    rgSource.Value = rgTo.Resize(1, rgSource.Columns.Count).Value
    On Error GoTo 0
'    rgSource.Copy
'    rgTo.PasteSpecial xlPasteValues

 

[szita2000]

Hey! I think I managed to solve it.

So I added an array. Loaded the source range in to it. Then I resize the rngto to as many columns as in the rgfrom and set the values to be equal to whatever is in the array.

Will mark the changes in the code

Sub ArchiveThisLine()
    
    Dim b As Range
    Dim lrto As Long, rgSource As Range, rgTo As Range, arr() As Variant '<---Dim the array
    
    Application.EnableEvents = False
    
    lrto = Sheet25.Range("A" & Rows.Count).End(xlUp).Row
    
    
    'Getting the Cell
    Set b = ActiveSheet.Buttons(Application.Caller).TopLeftCell
    
    'Set Cell value to nothing (There is a 1 placed there so we won't make hundreds of buttons if there are a button there already
    b.Value = ""
    
    
    'Copy the Name, Gépegység, Veszteség (Because we merging cells. - Nyilván. )
    Set rgSource = Sheet23.Range(Cells(b.Row, 2), Cells(b.Row, 4))
    Set rgTo = Sheet25.Range("A" & lrto + 1)
    
    On Error GoTo CustLabel
    arr() = rgSource.Value '<--Load the source range in to the array
    rgTo.Resize(1, rgSource.Columns.Count).Value = arr() '<--Resize the destination range and load the cells with the items in the array
    On Error GoTo 0
'    rgSource.Copy
'    rgTo.PasteSpecial xlPasteValues

 

[Peter_SSs]

You should not need to pass the values through an array.

First though, to make your code more robust, I would suggest adding the coloured code shown here

Set rgSource = Sheet1.Range(Sheet1.Cells(b.Row, 2), Sheet1.Cells(b.Row, 4))

The values should be able to be transferred to the new range thus:

rgTo.Resize(1, rgSource.Columns.Count).Value = rgSource.Value

 

[szita2000]

You should not need to pass the values through an array.

First though, to make your code more robust, I would suggest adding the coloured code shown here

Set rgSource = Sheet1.Range(Sheet1.Cells(b.Row, 2), Sheet1.Cells(b.Row, 4))

The values should be able to be transferred to the new range thus:

rgTo.Resize(1, rgSource.Columns.Count).Value = rgSource.Value

Oh! Thanks very much for that!

 

[Peter_SSs]

You're welcome.

 

[szita2000]

You're welcome.

Peter.

Can I just ask why in this instance it is not enough to describe the cell as a child of the parent (Sheet1.Range) object?

One would assume that you can define a cell as Sheet1.Range(cell(1,1)
And in the above instance b.row gives us the row number in the r1c1 address within the cell.

 

[Peter_SSs]

Can I just ask why in this instance it is not enough to describe the cell as a child of the parent (Sheet1.Range) object?

One would assume that you can define a cell as Sheet1.Range(cell(1,1)

If it is ever possible that Sheet1 is not the active sheet when the code gets to that line, then it will error as Cells(1,1), without the sheet qualification immediately before it, will refer to cell A1 on the Active Sheet, not on Sheet1. So the code would be trying to put cell A1 from the active sheet into a range on Sheet1 - resulting in an error.

If, on the other hand it is absolutely certain that Sheet1 will be the active sheet when that line is executed, then you don't need Sheet1 as a qualifier anywhere, just this would do

Set rgSource = Range(Cells(b.Row, 2), Cells(b.Row, 4))

 

[szita2000]

If it is ever possible that Sheet1 is not the active sheet when the code gets to that line, then it will error as Cells(1,1), without the sheet qualification immediately before it, will refer to cell A1 on the Active Sheet, not on Sheet1. So the code would be trying to put cell A1 from the active sheet into a range on Sheet1 - resulting in an error.

If, on the other hand it is absolutely certain that Sheet1 will be the active sheet when that line is executed, then you don't need Sheet1 as a qualifier anywhere, just this would do

Set rgSource = Range(Cells(b.Row, 2), Cells(b.Row, 4))

Thanks.

 

[Peter_SSs]

Cheers.