Copying every third cell across in a 1x13 range in one sheet and pasting onto a 5x1 range in another sheet

0
votes

I have 2 worksheets Sheet1 and Sheet2. Sheet1 has a range C12:O12 whilst Sheet2 has a range E4:E8. I need to copy the first and every third cell in range C12:O12 and paste into range E4:E8. Here is my code;

Sub PX() 'COPY DATA FROM ROW ONE SHEET INTO A COLUMN on another sheet
Sheets("Sheet2").Range("E4:E8").Value = Application.WorksheetFunction.Transpose(Sheets("Sheet1").Range("C12:O12").Offset(0, 3).Value)
End Sub

However, it just seems to transpose Range("C12:O12") and paste the range("C12:G12"). Can you please help?

1
vbaexcel

1 Answers

0
votes

3try this:

Sub main()
Dim i As Integer
For i = 1 To 5
    Sheet2.Cells(i + 3, 5) = Sheet1.Cells(12, (i - 1) * 3 + 3)
Next i
End Sub