Copy offset range

Question

I have VBA macro to copy paste cells. The problem is that I have to copy this macro like 30 times and go through each case. I have same amount of rows in between, only starting row is changing. I would like to modify my macro to "copy offset" from the first row. So in this particular case anchor cell is D13.

I have tried to use copySheet.Offset(17, 1).Copy ("D14:E30") but this does not seems to work?

Sub CopyPaste()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Calculation")
Set pasteSheet = Worksheets("Calculation")

Range("D13").MergeArea.Copy
pasteSheet.Cells(13, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial xlPasteAll

copySheet.Range("D14:E30").Copy
pasteSheet.Cells(13, Columns.Count).End(xlToLeft).Offset(1, 0).PasteSpecial xlPasteAll

Range("D31").MergeArea.Copy
pasteSheet.Cells(13, Columns.Count).End(xlToLeft).Offset(18, 0).PasteSpecial xlPasteAll

copySheet.Range("D32:E33").Copy
pasteSheet.Cells(13, Columns.Count).End(xlToLeft).Offset(19, 0).PasteSpecial xlPasteAll

Range("D163").MergeArea.Copy
pasteSheet.Cells(13, Columns.Count).End(xlToLeft).Offset(150, 0).PasteSpecial xlPasteAll

copySheet.Range("D164:E167").Copy
pasteSheet.Cells(13, Columns.Count).End(xlToLeft).Offset(151, 0).PasteSpecial xlPasteAll

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Answer 1

To get from Range("D13") to Range("D14:E30") you can use a combination of the Range.Offset property and the Range.Resize property.

Dim StartRange As Range
Set StartRange = copySheet.Range("D13")

'D14:E30
StartRange.Offset(1, 0).Resize(17, 2).Copy

'D32:E33
StartRange.Offset(19, 0).Resize(2, 2).Copy

You can adapt that to your other copy statements. So if you change your StartRange all the others change accordingly.

Answer 2

copySheet.Offset(1, 1).Copy does not work, because .Offset() needs Cell or Range reference.

E.g. copySheet.Range("A10").Offset(1, 1).Copy

---

If you want to copy offset from D13:E40, then this is a possible option:

copySheet.Range("D13:E40").Offset(17, 1).Copy