none
Range of Values referencing RRS feed

  • Question

  • Hello guys,

    I just want to check how get range of values by just referencing to it.

    What i mean is:

    This line below will copy range of cells (it from Col A3:A4 to Col B3:B4)
    Worksheets("Sheet1").Range(Cells(4, 1), Cells(3, 2)).Copy

    This line below will copy the range of cells above starting at Col A row 4:

    Worksheets("sheet2").Cells(4, 1).PasteSpecial Paste:=xlPasteValues

     

    Question is, how to achieve same result by just referencing the values?

    Code below will just copy a single cell, instead of the whole range

    Worksheets("sheet2").Cells(4, 1).value = Worksheets("Sheet1").Range(Cells(4, 1), Cells(3, 2)).value

     

    any help is greatly appreciated. Thanks. :)


    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.


    • Edited by cguan Monday, May 8, 2017 4:44 AM edit
    Monday, May 8, 2017 4:44 AM

Answers

  • You could use

    Worksheets("Sheet2").Cells(4, 1).Resize(2, 2).Value = Worksheets("Sheet1").Cells(3, 1).Resize(2, 2).Value

    By the way, Worksheets("Sheet1").Range(Cells(4, 1), Cells(3, 2)) is only valid if Sheet1 is the active sheet. If it is, you might as well use Range(Cells(4, 1), Cells(3, 2)), but if it isn't, you should use

    Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(4, 1), Worksheets("Sheet1").Cells(3, 2))

    or simply

    Worksheets("Sheet1").Range("A3:B4")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by cguan Monday, May 8, 2017 9:16 AM
    Monday, May 8, 2017 6:02 AM

All replies

  • You could use

    Worksheets("Sheet2").Cells(4, 1).Resize(2, 2).Value = Worksheets("Sheet1").Cells(3, 1).Resize(2, 2).Value

    By the way, Worksheets("Sheet1").Range(Cells(4, 1), Cells(3, 2)) is only valid if Sheet1 is the active sheet. If it is, you might as well use Range(Cells(4, 1), Cells(3, 2)), but if it isn't, you should use

    Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(4, 1), Worksheets("Sheet1").Cells(3, 2))

    or simply

    Worksheets("Sheet1").Range("A3:B4")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by cguan Monday, May 8, 2017 9:16 AM
    Monday, May 8, 2017 6:02 AM
  • Thank you, Hans.



    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Monday, May 8, 2017 9:16 AM