复印偏移范围 [英] Copy offset range

问题描述

我有VBA宏可以复制粘贴单元格.问题是我必须像30次一样复制此宏，并逐一检查每种情况.我之间有相同数量的行，只有开始的行在变化.我想将宏修改为从第一行开始的复制偏移量".因此，在此特定情况下，锚点单元格为D13.

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.

我尝试使用copySheet.Offset(17, 1).Copy("D14:E30")，但这似乎不起作用吗?

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

推荐答案

要从Range("D13")转到Range("D14:E30")，可以使用

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

您可以使其适应其他复制语句.因此，如果您更改StartRange，其他所有内容也会相应更改.

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

这篇关于复印偏移范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！