Need help Creating a Loop

Very new to VBA. I've recorded a macro to reference data on Sheet1 and pull it over to Sheet2 in a better, more organized format. Now, I need to somehow increment all those references down by 6 rows and continue until there is no more data present on Sheet1(or Row 4609). Can anyone help me put together something in VB to do this? Your help is greatly appreciated.

Here is the macro I recorded:


Sub format_data()
'
' format_data Macro
'

'
ActiveCell.FormulaR1C1 = "=Sheet1!RC"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[-2]"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[-2]"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[-2]"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[-2]"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[-1]"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[-3]"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[-6]"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[-6]"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[-6]"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[-6]"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[-6]"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[-6]"
Range("O2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[-7]"
Range("P2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[4]C[-8]"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[4]C[-15]"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[4]C[-15]"
Range("S2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[4]C[-15]"
Range("T2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[4]C[-15]"
Range("U2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[4]C[-15]"
Range("V2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[4]C[-15]"
Range("A3").Select
End Sub

So you are just trying to move data from sheet1 to sheet2 , is that what you are trying to do. If not explain in detail.

Originally Posted by anilsolipuram

So you are just trying to move data from sheet1 to sheet2 , is that what you are trying to do. If not explain in detail.

Yes, that's correct. The data on Sheet1 is not contiguous, so I need to move it over to Sheet2 so that it is more organized and useable.

Try this macro it is going to just copy the data from sheet1 to sheet2


Sub Macro8()
Sheets("Sheet1").Select
used_range = ActiveSheet.UsedRange.Address
t = Split(used_range, ":")
Range("B2:" & t(1)).Select
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
End Sub

Thanks, unfortunately this brings it over in the same unorganized format that it is on Sheet1.

My problem is analogous to this example...Let's say you have a mailing address that is out of order on Sheet1, and you want to organize it in the normal fashion on Sheet2. For example, on Sheet 1, the order might looks like this: City(Row 1), ZIP Code(Row 2), Name(Row 3), Street(Row 4), State(Row 5). What I'm looking to do is organize it in a standard fashion like this: Name, Street, City, State, ZIP Code(all on the same Row, Row 1 on Sheet2).

Does that make sense? If not, I'll try to explain a little better what I'm trying to do.

I got what you want ,but to have a clear picture email you workbook to [email protected] showing sample data in sheet1 and sheet2(how the data looks).

I want to see how the data looks in sheet2 when I compare to sheet1

Originally Posted by anilsolipuram

I got what you want ,but to have a clear picture email you workbook to [email protected] showing sample data in sheet1 and sheet2(how the data looks).

I want to see how the data looks in sheet2 when I compare to sheet1

Email sent. Thanks so much for your help!