HELP with looping a macro

frogsplash23

New Member
Joined
Apr 11, 2011
Messages
1
Hello all,
I am struggling with how to insert the coding into an existing macro to loop it. I am not very experienced with VBA, the macro itself works great for the first row but I want it to do it for the entire range.

I have a table of data, i want to copy data within certain cells on a row on to a different sheet at ten row intervals.I want it to continue to do this until it hits an empty cell from the range on the first sheet.

For example,
Sheet 1
A B C
1 347 10 Weekly
2 348 10 Monthly
3 349 10 Weekly
4

Sheet 2
A B
10 Weekly
20 Monthly
20 Weekly


So as above it will look at Cell A1 and copy the value in C1 and paste it onto row 10 of sheet 2 it will continue to do this for this range of cells until it hits an empty cell, i.e A4.

I am aware it will likely be a Do Until formula but not sure exactly how to word it for the above.

Any help with this would be greatly appreciated

Thanks
Chris
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub test()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR Step 10
        .Range("B" & i).Resize(, 2).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,322
Messages
5,528,007
Members
409,798
Latest member
Snake68

This Week's Hot Topics

Top