使用“for .... next”复制粘贴范围，步骤为

Question

我想使用for..next在步骤之间复制一个范围之间的范围，但我不熟练使用for..next语句。我已经记录了与宏的步骤，这里是代码：

Sub Macro1() 
    Range("A2:A22").Select 
    Selection.Copy 
    Sheets("Sheet4").Select 
    ActiveSheet.Paste 
    Sheets("db_pivot").Select 
    Range("C2:C22").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Sheets("Sheet4").Select 
    Range("B2").Select 
    ActiveSheet.Paste 
    Sheets("db_pivot").Select 
    Range("E2:E22").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Sheets("Sheet4").Select 
    Range("C2").Select 
    ActiveSheet.Paste 
    Sheets("db_pivot").Select 
    Range("G2:G22").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Sheets("Sheet4").Select 
    Range("D2").Select 
    ActiveSheet.Paste 
End Sub 

你可以用for..next语句与步骤转换代码？

Answer 1

您可以在一个范围使用Uninon，以便将非连续列。

Option Explicit 

Sub Macro1() 

Dim DBPivotSht As Worksheet 
Dim Sht4 As Worksheet 
Dim UnionRng As Range 

' get used to allways define your worksheet objects 
Set DBPivotSht = Worksheets("db_pivot") 
Set Sht4 = Worksheets("Sheet4") 

With DBPivotSht 
    Set UnionRng = Union(.Range("A2:A22"), .Range("C2:C22"), .Range("E2:E22"), .Range("G2:G22")) 
End With 

' copy the entire Union range and paste in "Sheet4" 
UnionRng.Copy Destination:=Sht4.Range("A2") 

End Sub 

Answer 2

没有For ... Next语句，我建议这个代码

With Sheets("db_pivot") 
    .Range("A2:A22").Copy Sheets("Sheet4").Range("A1") 
    .Range("C2:C22").Copy Sheets("Sheet4").Range("B2") 
    .Range("E2:E22").Copy Sheets("Sheet4").Range("C2") 
    .Range("G2:G22").Copy Sheets("Sheet4").Range("D2") 
End With 

HTH 乔恩