Return the values in another sheet
 

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...

Return the values in another sheet
 

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

Return the values in another sheet
 

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.

On Feb 11, 9:03*pm, marcus wrote:
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

Return the values in another sheet
 

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