Excel VBA defining a .Range with .Cells
26,412
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
- 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 aboutWith...End With
statements here: http://msdn.microsoft.com/en-us/library/office/gg264723(v=office.15).aspx) - By naming the
Ranges
you can use the super-friendlyRange.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.
![Neat Machine](https://i.stack.imgur.com/znXui.jpg?s=256&g=1)
Author by
Neat Machine
Updated on November 12, 2020Comments
-
Neat Machine over 3 years
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.