CODEWITHSUNDEEP
vbaexcel
Magma
Magma

Reputation: 3

Paste values in first row and then every fourth row in excel

I have a workbook with with a range of values (K3:K89) that I want to transform with some formulas and then paste somewhere in the workbook. The problem is that I want to be able to paste these values in the first row and then every fourth row. This is the code I have so far.

Sub Copy()

Range("K3:K89").Select
ActiveWindow.SmallScroll Down:=-60
Selection.Copy
Range("R3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("L3").Select
Sheets("Sheet2").Select
End Sub

I can´t solve the part of pasting the values every fourth row. Help would be so appreciated.

Upvotes: 0

Views: 833

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

How is this? I am assuming your column of data is in Column K and you want the data to be pasted into column R.

Sub test()
Dim firstRow As Integer, lastRow As Integer, totalLines As Integer, i As Integer, k As Integer
Dim ws As Worksheet

Set ws = ActiveSheet

k = 0
firstRow = 3
lastRow = Cells(3, 11).End(xlDown).Row
' how many total values do you have?
totalLines = lastRow - firstRow + 1

'Now, loop through the range of values, pasting to Column 18 (column R), skip 3 rows and repeat
For i = 0 To totalLines
    Cells(3 + k, 18).Value = Cells(3 + i, 11).Value
    k = k + 4 ' add 4 to k, to skip 4 more lines
Next i

End Sub

Upvotes: 1

Related Questions