Set Cell Value Equal to Another using VBA

[Guner]

Hi all,

I was trying to figure out how to set value for sheet1.range("F:X") equal to sheet2.range("A:T") with syntax:

sheet1.range(cells(4,6),cells(4,24))=sheet2.range(cells(2,2),cells(2,20)).value 

The reason I'm using cells instead of range is the variable of row I'll need to pass to the code above.
But getting nowhere other than the error message that keeps telling me range class failed sort of thing.

What is proper syntax to get the result?

[Trebor76]

Hi Guner,

You can use variables in ranges, i.e.

Sub Macro2() 
    Dim lngRowSource As Long, _ 
    lngRowDestin As Long 
     
    lngRowSource = 2 
    lngRowDestin = 4 
     
    Sheets("Sheet1").Range("F" & lngRowDestin & ":X" & lngRowDestin & "").Value = _ 
    Sheets("Sheet2").Range("B" & lngRowSource & ":T" & lngRowSource & "").Value 
     
End Sub 

HTH

Robert

[Barb-B]

The .cells property has some logic that makes it a little awkward for what you're trying to do. Basically, every time you reference a range using the cells([row],[column]) notation, VBA assumes you are refering to the active worksheet, unless otherwise specified immediately before the cells reference. When you hit this line of code, if either Sheet1 or Sheet2 is the active sheet, you could eliminate that qualifier from its side of the equation. Here's what it looks like with sheet specified in the necessary places:

Range(Sheet1.Cells(4, 6), Sheet1.Cells(4, 24)) = Range(Sheet2.Cells(2, 2), Sheet2.Cells(2, 20)).Value 

It's kind of a mouthful. Might be easer to assemble to assemble an address string instead.

[Guner]

Yeah, both works. Just personally I prefer .cells over range is you can pass variable directly instead to code the string address.
Thank you two for saving my time again.