vba 如何在Excel中一次在数据之间粘贴多次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13411272/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to paste multiple times at once in Excel between data
提问by Wakanina
Say I have data like this
说我有这样的数据
A B C
1 Jim 1 10
2 Jim 2 20
3 Jim 3 30
4 Tom 1 20
5
6
7 Jos 1 15
8
9
What I want is copy the row 2 and 3 then paste to row 5 and 6, and then 8 and 9, and so on. The blank rows are always 2.
我想要的是复制第 2 行和第 3 行，然后粘贴到第 5 行和第 6 行，然后是第 8 行和第 9 行，依此类推。空白行始终为 2。
There is a formula in the cell 2C and 3C that calculate the value in column B multiplied by cell C1.
单元格 2C 和 3C 中有一个公式，用于计算 B 列中的值乘以单元格 C1。
So, the result would be like this
所以，结果会是这样的
A B C
1 Jim 1 10
2 Jim 2 20
3 Jim 3 30
4 Tom 1 20
5 Tom 2 40
6 Tom 3 60
7 Jos 1 15
8 Jos 2 30
9 Jos 3 45
The problem is I have to paste hundreds times that would be time consuming.
问题是我必须粘贴数百次，这会很耗时。
I would appreciate if anyone can help.
如果有人可以提供帮助，我将不胜感激。
采纳答案by CustomX
Please keep in mind:
铭记于心：
You'll need to make sure you have a value in column A1, otherwise just increase the value of
ito the second row with data.Varis a dummy variable used to keep track when a new name occurs.firstValuewill contain the rownumber of the first row per name.
您需要确保在 A1 列中有一个值，否则只需将 的值
i增加到带有数据的第二行。Var是一个虚拟变量，用于在新名称出现时进行跟踪。firstValue将包含每个名称的第一行的行号。
Code
代码
Sub test()
Var = 0
LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 2
For i = 2 To LastRow
If Range("A" & i).Value = "" Then
If Var = 0 Then
firstValue = i - 1
Range("A" & i).Value = Range("A" & i - 1).Value
Range("B" & i).Value = Range("B" & i - 1).Value + 1
Range("C" & i).Value = Range("C" & firstValue).Value + Range("C" & i - 1).Value
Var = Var + 1
Else
Range("A" & i).Value = Range("A" & i - 1).Value
Range("B" & i).Value = Range("B" & i - 1).Value + 1
Range("C" & i).Value = Range("C" & firstValue).Value + Range("C" & i - 1).Value
Var = Var + 1
End If
Else
Var = 0
End If
Next i
End Sub
回答by grozhd
Sub MakeRows()
Dim Sh As Worksheet, DataRng As Range, CopyRng As Range, TargetRng As Range
Set Sh = Sheets(1) 'select your sheet here
'you can also do it like this = Worksheets("name")
Set DataRng = Sh.Range("B2")
Set TargetRng = Sh.Range("A4")
While IsEmpty(TargetRng) = False
TargetRng.Copy TargetRng.Offset(1, 0)
TargetRng.Copy TargetRng.Offset(2, 0)
Set CopyRng = Sh.Range(DataRng, DataRng.Offset(1, 1))
CopyRng.Copy TargetRng.Offset(1, 1)
Set TargetRng = TargetRng.Offset(3, 0)
Set DataRng = DataRng.Offset(3, 0)
Wend
End Sub
Works with relative references in cells 2C and 3C.
使用单元格 2C 和 3C 中的相对引用。
