Excel VB - Copy/Paste cells: How can I increment the range of to/from cells

Question

Brand new to VB and here's what I've written to copy a range of cells from one worksheet to another (I know it's bad but it works for a single iteration).

The problem is that I have 65 rows to copy from one worksheet to another. One iteration amounts to copying a single row with 56 columns (56 cells) into a second worksheet where the configuration is 8 rows by 7 cols. I've been watching videos and reading but I can't figure out how to copy efficiently or even with my inefficient code, can't figure out how to increment all cell values by one and loop.

Any suggestion on the functions I should be reading about to help me through this?

Sub Copytax()

    Worksheets("Sheet3").Activate
    Range("A5:G5").Select
    Selection.Copy
    Worksheets("TaxFormat").Activate
    Range("E34:K34").PasteSpecial

    Worksheets("Sheet3").Activate
    Range("H5:N5").Select
    Selection.Copy
    Worksheets("TaxFormat").Activate
    Range("E35:K35").PasteSpecial

    Worksheets("Sheet3").Activate
    Range("O5:U5").Select
    Selection.Copy
    Worksheets("TaxFormat").Activate
    Range("E36:K36").PasteSpecial

    Worksheets("Sheet3").Activate
    Range("V5:AB5").Select
    Selection.Copy
    Worksheets("TaxFormat").Activate
    Range("E37:K37").PasteSpecial

    Worksheets("Sheet3").Activate
    Range("AC5:AI5").Select
    Selection.Copy
    Worksheets("TaxFormat").Activate
    Range("E38:K38").PasteSpecial

    Worksheets("Sheet3").Activate
    Range("AJ5:AP5").Select
    Selection.Copy
    Worksheets("TaxFormat").Activate
    Range("E39:K39").PasteSpecial

    Worksheets("Sheet3").Activate
    Range("AQ5:AW5").Select
    Selection.Copy
    Worksheets("TaxFormat").Activate
    Range("E40:K40").PasteSpecial

    Worksheets("Sheet3").Activate
    Range("AX5:BD5").Select
    Selection.Copy
    Worksheets("TaxFormat").Activate
    Range("E41:K41").PasteSpecial

End Sub

score 1 · Answer 1 · edited May 23 '17 at 11:58

After reading How to avoid using Select in Excel VBA macros, it is not difficult to convert your code to:

Sub Copytax()
    Worksheets("Sheet3").Range("A5:G5").Copy Worksheets("TaxFormat").Range("E34:K34")
    Worksheets("Sheet3").Range("H5:N5").Copy Worksheets("TaxFormat").Range("E35:K35")
    Worksheets("Sheet3").Range("O5:U5").Copy Worksheets("TaxFormat").Range("E36:K36")
    Worksheets("Sheet3").Range("V5:AB5").Copy Worksheets("TaxFormat").Range("E37:K37")
    Worksheets("Sheet3").Range("AC5:AI5").Copy Worksheets("TaxFormat").Range("E38:K38")
    Worksheets("Sheet3").Range("AJ5:AP5").Copy Worksheets("TaxFormat").Range("E39:K39")
    Worksheets("Sheet3").Range("AQ5:AW5").Copy Worksheets("TaxFormat").Range("E40:K40")
    Worksheets("Sheet3").Range("AX5:BD5").Copy Worksheets("TaxFormat").Range("E41:K41")
End Sub

From this point you can leave it as is, or convert to a loop that copies 7 cells each time:

Dim i As Long
Dim SourceRange As Range, FirstTargetRange As Range

Set SourceRange = Worksheets("Sheet3").Range("A5:BD5")
Set FirstTargetRange = Worksheets("TaxFormat").Range("E34:K34")

For i = 1 To SourceRange.Cells.Count Step 7
  SourceRange.Cells(1, i).Resize(, 7).Copy FirstTargetRange.Offset(i \ 7, 0)
Next

Excel VB - Copy/Paste cells: How can I increment the range of to/from cells

1 Answers1