VBA 从不同的范围复制粘贴值并粘贴在同一张纸上，相同的行偏移列（对多张纸重复）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21869053/
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
VBA Copy Paste Values From Separate Ranges And Paste On Same Sheet, Same Row Offset Columns (Repeat For Multiple Sheets)
提问by user979226
I was going to make a Case statement but I don't think that makes much sense in this situation, I'm a VBA n00b, since this workbook will remain quite static I don't mind taking the non-optimal approach and record a macro of me copying and pasting but I thought I'd ask here before I land on that.
我打算做一个案例陈述，但我认为在这种情况下这没有多大意义，我是 VBA n00b，因为这本工作簿将保持相当静态我不介意采用非最佳方法并记录一个我复制和粘贴的宏，但我想在我登陆之前我会在这里问。
I have 6 worksheets in 1 workbook.
我在 1 个工作簿中有 6 个工作表。
Sheet1: Copy BA17:BI31, Copy BA48:BI50, Copy BA67:BI81, Copy BA98:BI100, Copy BA117:BI131, Copy BA148:BI150, Copy BA167:BI181, Copy BA198:BI200, Copy BA215:BI215, Copy BA230:BI230, Copy BA246:BI260, Copy BA275:BI277
表1：副本BA17：BI31，副本BA48：BI50，副本BA67：BI81，副本BA98：BI100，副本BA117：BI131，副本BA148：BI150，副本BA167：BI181，副本BA198：BI200：副本BA225，副本BA225： BI230, 复制 BA246:BI260, 复制 BA275:BI277
And paste the above copies into the identical rows, however in columns AE:AM of the same sheet (simply offset).
并将上述副本粘贴到相同的行中，但是在同一张纸的 AE:AM 列中（只是偏移）。
If someone can steer me in the right direction for this I could repeat that solution for the other 5 sheets where I have to do the same idea but for different row and columns.
如果有人可以为此引导我朝着正确的方向前进，我可以对其他 5 张纸重复该解决方案，在那里我必须对不同的行和列执行相同的想法。
Any help would be appreciated, thanks!
任何帮助将不胜感激，谢谢！
Sub CopyPasteOffetColumns()
Range("BA17:BI31").Select
Application.CutCopyMode = False
Selection.Copy
Range("AE17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BA48:BI50").Select
Application.CutCopyMode = False
Selection.Copy
Range("AE48").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BA67:BI81").Select
Application.CutCopyMode = False
Selection.Copy
Range("AE67").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BA98:BI100").Select
Application.CutCopyMode = False
Selection.Copy
Range("AE98").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BA117:BI131").Select
Application.CutCopyMode = False
Selection.Copy
Range("AE117").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BA148:BI150").Select
Application.CutCopyMode = False
Selection.Copy
Range("AE148").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BA167:BI181").Select
Application.CutCopyMode = False
Selection.Copy
Range("AE167").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BA198:BI200").Select
Application.CutCopyMode = False
Selection.Copy
Range("AE198").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BA215:BI215").Select
Application.CutCopyMode = False
Selection.Copy
Range("AE215").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BA230:BI230").Select
Application.CutCopyMode = False
Selection.Copy
Range("AE230").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BA246:BI260").Select
Application.CutCopyMode = False
Selection.Copy
Range("AE246").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BA275:BI277").Select
Application.CutCopyMode = False
Selection.Copy
Range("AE275").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
采纳答案by Simon1979
Something like the following would suffice:
类似以下内容就足够了：
Sub CopyPasteOffetColumns()
Dim rng As Range
Set rng = Range("BA17:BI31")
With rng
.Copy
.Offset(0, -22).PasteSpecial (xlPasteValues)
End With
Set rng = Range("BA48:BI50")
With rng
.Copy
.Offset(0, -22).PasteSpecial (xlPasteValues)
End With
'Repeat for each range
End Sub
Generally you would use code like this to make it more dynamic, if you have a criteria to select which rows to copy. For example if you want to copy everything where the value in column BA equals '1234' (this can be any kind of criteria I have just picked a nice simple one) then the below would cycle through column BA and copy all the rows where BA = 1234:
通常，如果您有选择要复制的行的条件，您会使用这样的代码使其更具动态性。例如，如果您想复制 BA 列中的值等于“1234”的所有内容（这可以是任何类型的标准，我刚刚选择了一个简单的标准），那么下面将循环遍历 BA 列并复制 BA 所在的所有行= 1234：
Sub CopyPasteOffetColumns()
Dim rng As Range, c As Range
Dim sh As Worksheet
Set sh = ActiveSheet
' Set the range to be the used cells in column BA (starting from BA1)
Set rng = Range("BA1:BA" & sh.Cells(sh.Rows.Count, "BA").End(xlUp).Row)
' Cycle through the cells and apply the criteria
For Each c In rng
If c.Value = 1234 Then ' change criteria as required
Range(c.AddressLocal, c.Offset(0, 8).AddressLocal).Copy
c.Offset(0, -22).PasteSpecial xlPasteValues
End If
Next c
End Sub
