How can I copy&paste to columns?

Question

I have made some model in Excel, where I first have to manually copy values from H24:H25 and special paste it to B29:B30 and then I have to copy values from B29:B30 to the table with this range: G30:M31, but in first loop I need to paste it to G30:G31 (it is 1st year), in second to H30:31 ... and in last loop i need to paste from B29:30 to M30:M31 (7th year)

Sub MAKRO()
    Dim Year As Integer
    For Year = 1 To 7
    Range("K45") = Year
    Range("H24:H25").Select
    Selection.Copy
    Range("B29:B30").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("B29:B30").Select
    Selection.Copy
    Range(I cant figure out this part)
    Selection.PasteSpecial Paste:=xlPasteValues
    Next Year
End Sub

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, May 08 '19 at 06:40

score 0 · Answer 1 · answered May 07 '19 at 19:05

A better way of writing this would be something like the following:

Dim WS as worksheet

Set WS = Thisworkbook.sheets("Sheet Name")
    For i = 1 to 7
        WS.Range(YearRange) = i
        WS.Range(DestinationRange).Offset(0,I).value2 = WS.Range(SourceRange).value2
    Next i

The rest (and how exactly to use offset) is left as an exercise for the reader... (Aka I'm quickly freehanding this code)

Please note that you do need to fill in the destination range (like A1:A2)

How can I copy&paste to columns?

1 Answers1