Return the values in another sheet

[Steve]

I have 2 sheets in a workbook. I am trying to code a procedure which
will cause the value of Sheet 2 to return the value contained is Sheet
1. Each row increments by 4 (ie - B4, B8, B12, B16...) and starts at
row 4, except for B5.

Sheet 2 A1 = value in sheet1 B4
Sheet 2 B1 = value in sheet1 C4
Sheet 2 C1 = value in sheet1 B5

Sheet 2 A2 = value in sheet1 B8
Sheet 2 B2 = value in sheet1 C8
Sheet 2 C2 = value in sheet1 B9

Sheet 2 A3 = value in sheet1 B12
Sheet 2 B3 = value in sheet1 C12
Sheet 2 C3 = value in sheet1 B13

And so on, until Sheet 1 ends, around row 2,500.

Thanks in advance for any assistance...

 

[marcus]

Hi Steve

This should do the trick. I assume you are in Sheet2 when this is
run.

Take care

Marcus

Sub Down4Rows()

Dim i As Long, j As Long, lw As Long
'Run in sheet2
Application.ScreenUpdating = False
'Last row of sheet 1
lw = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
j = 4 ' the 4th row in sheet1
For i = 1 To lw
Range("A" & i).Value = Sheets("Sheet1").Range("B" & j).Value
Range("B" & i).Value = Sheets("Sheet1").Range("C" & j).Value
Range("C" & i).Value = Sheets("Sheet1").Range("D" & j).Value
'etc etc
j = j + 4
Next i
Application.ScreenUpdating = True

End Sub

 

[Steve]

Thanks Marcus.

One small problem though. C1 needs to first equal the value in B5, not
D4, and then every 4th row after that (B5, B9, B13, B17, etc...)
It's a little different than the other 2.

 

[marcus]

Hi Steve

My mistake. I made an assumption. Replace the third line in the loop
with this;

Range("C" & i).Value = Sheets("Sheet1").Range("B" & j + 1).Value

That should get the job done.

Take care

Marcus