none
VBA to copy a range from sheet 6 to a range on sheet 1 RRS feed

  • Question

  • Is there a VBA statement that will copy a range, like C2:C1101 on sheet 6 to E21:E1121 on sheet 1, where nulls stay as nulls?

    Sunday, January 25, 2015 6:12 PM

Answers

  • Yes, like this:

    Worksheets("Sheet6").Range("C2:C1101").Copy Destination:=Worksheets("Sheet1").Range("E21")

    Modify the sheet names to match your workbook.


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

    • Marked as answer by Mark Matzke Monday, January 26, 2015 2:42 PM
    Sunday, January 25, 2015 8:09 PM

All replies

  • Yes, like this:

    Worksheets("Sheet6").Range("C2:C1101").Copy Destination:=Worksheets("Sheet1").Range("E21")

    Modify the sheet names to match your workbook.


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

    • Marked as answer by Mark Matzke Monday, January 26, 2015 2:42 PM
    Sunday, January 25, 2015 8:09 PM
  • That would be awesome, but sheet1 has merged cells (sheet6 is not merged), so the copy command fails. 7 thousand lines of script to set them one at a time (my hope was to do 7 columns).
    Sunday, January 25, 2015 9:58 PM
  • That is one of the many reasons to avoid merging cells. They play havoc with selecting ranges, sorting ranges, copying and pasting ranges and VBA.

    If you have horizontally merged cells, I'd unmerge them, and set the horizontal alignment to Center Across Selection.

    If you have vertically merged cells, there is no easy workaround


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

    Sunday, January 25, 2015 10:26 PM