Offset and resize with vba

Question

I am having some problems with what I think is a simple vba command:

Worksheets("Sheet").Range("namedrange_d").Resize(0, 4).Offset(6, 0).Copy _
  Destination:=Worksheets("Sheet1").Range("namedrange").Resize(0, 4).Offset(6, 0)

I want to copy a defaults range 5 cells wide, that is 7 cells below a reference cell (namedrange_d). What is the problem with the syntax?

Answer 1

Zero is not a valid argument for resize. If you want to keep the original size of the range, simply omit the argument. Otherwise you need to specify the number of rows and columns explicitly. Here's how you would keep the original number of rows

Worksheets("Sheet").Range("namedrange_d").Resize(, 4).Offset(6, 0).Copy _
  Worksheets("Sheet1").Range("namedrange").Resize(, 4).Offset(6, 0)

Answer 2

I would do the following (I am being very explicit - this tends to make for code that is easier to read and debug, with minimal speed impact. It is not "clever".):

Dim dataSource As Range
Dim firstCellSource, firstCellDest As Range

Set firstCellSource = Worksheets("Sheet1").Range("namedrange_d").Offset(6, 0)
Set firstCellDest = Worksheets("Sheet1").Range("namedrange").Offset(6, 0)

Set dataSource = Range(firstCellSource, firstCellSource.Offset(0, 4))

dataSource.Copy Destination:=firstCellDest

Like this the code is essentially "self documenting" and it's easy to see what you are doing. Note you only need to give the first cell of the destination.