2

I'm running into a problem trying to select/copy certain ranges within my worksheet. I've narrowed it down as best as I can.

This works fine:

dataSheet.Cells(dRow, dataSheet.Range("IO_MODULES").Column).Select

This does not:

dataSheet.Range(Cells(dRow, dataSheet.Range("IO_MODULES").Column), Cells(dRow, dataSheet.Range("IO_MODULES").Column)).Select

This is what I'm trying to do:

dataSheet.Range(Cells(dRow, dataSheet.Range("IO_MODULES").Column), Cells(dRow, dataSheet.Range("MODULE_END").Column)).Copy Destination:= _
dataSheet.Range(Cells(dataSheet.Range("MODULE_1").Row + i - 1, dataSheet.Range("D_COUNT").Column + 1), Cells(dataSheet.Range("MODULE_1").Row + i - 1, dataSheet.Range("DATA_COL_END").Column))

I seem to be misunderstanding the relationship between Worksheet.Cells and Worksheet.Range. What I need to do is be able to select a range of cells based on the row and column numbers of cells that I've named.

Neat Machine
  • 681
  • 4
  • 11
  • 18
  • 2
    On the whole, you're going to want to try and avoid using `.Select` -- here's a nice write-up about that: http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select – Dan Wagner May 27 '14 at 15:37
  • 2
    `Cells` is an unqualified range object, so it always is implicitly defined as `ActiveSheet.Cells`. Therefore, you get an error with `dataSheet.Range(Cells(...` unless `dataSheet` **is** the `ActiveSheet`. – David Zemens May 27 '14 at 15:49
  • Wow, can't believe I overlooked that. Not sure what I was thinking using Cells like I was. Thanks – Neat Machine May 27 '14 at 15:57

1 Answers1

4

UNTESTED try this to copy from one Range to another:

'...
Dim Source As Range, Dest As Range

With dataSheet
    Set Source = .Range(.Cells(dRow, .Range("IO_MODULES").Column), _
        .Cells(dRow, .Range("MODULE_END").Column))
    Set Dest = .Range(.Cells(.Range("MODULE_1").Row + i - 1, .Range("D_COUNT").Column + 1), _
        .Cells(.Range("MODULE_1").Row + i - 1, .Range("DATA_COL_END").Column))
    Source.Copy Dest
End With
  1. The With...End With is in place to allow you to operate many times on a single object (in this case, dataSheet) without calling it explicitly every time. Every line of code you write is a line that must be maintained and potentially debugged, so when you can be more concise without losing readability you should do so. (More about With...End With statements here: http://msdn.microsoft.com/en-us/library/office/gg264723(v=office.15).aspx)
  2. By naming the Ranges you can use the super-friendly Range.Copy method, outlined here: http://msdn.microsoft.com/en-us/library/office/ff837760(v=office.15).aspx. This enables you to bypass a .Select or .Activate, which in my experience is a major source of run-time errors.
Dan Wagner
  • 2,693
  • 2
  • 13
  • 18