simple range copy issue

Question

I know I'm being dense but I can't for the life of me find the right syntax for a simple (clipboard free) copy action:

Sheets("B").Range(Cells(X, Y), Cells(X, Y+ 3)).value = _
Sheets("A").Range(Cells(1, Z), Cells(1, Z+ 3)).value

score 2 · Accepted Answer · answered Apr 19 '17 at 15:14

2

You would need to qualify one or both of the uses of Cells so that VBA knows what sheet contains those cells:

Sheets("B").Range(Sheets("B").Cells(X, Y), Sheets("B").Cells(X, Y+ 3)).Value = _
Sheets("A").Range(Sheets("A").Cells(1, Z), Sheets("A").Cells(1, Z+ 3)).Value

answered Apr 19 '17 at 15:14

John Coleman

51,337
7
54
119

You beat me by 4 seconds. 8P – Scott Craner Apr 19 '17 at 15:15
@ScottCraner It is a rare day when I answer a question faster than you :) – John Coleman Apr 19 '17 at 15:16

score 0 · Answer 2 · answered Apr 19 '17 at 15:19

I would start from a known reference (like "A1") and use the .Offset() and .Resize() methods for "selecting" the values you want. This way you are guaranteed to get the same number of rows and columns.

Sheets("A").Range("A1").Offset(0,Z-1).Resize(1,3).Value = _ 
  Sheets("B").Range("A1").Offset(X-1,Y-1).Resize(1,3).Value

This works best in combination with named ranges in the worksheet

Sheets("A").Range("TableA").Offset(0,X-1).Resize(n,1).Value = _
  Sheets("B").Range("TableB").Offset(0,Y-1).Resize(n,1).Value

for copying n rows from TableB column# X onto TableA column# Y

simple range copy issue

2 Answers2