how do i take this long code and turn it into a loop?

[bigdan]

Hi guys. I don't really know VBA except for a little bit here and there so I'm hoping to get some advice here. This is mainly me recording a macro and then editing the code to get it to do what I want. This essentially gets the job done but I know it could be made much cleaner with loops. Which I don't know how to do right now.

Here's what this code actually does. There is data that starts in approximately column F and will always come 2 columns at a time but the number of rows is not consistent, it could be as little as two or as many as 20. So the first set of data would be in approximately column F the second set might be four columns to the right of that, then the third set might be another few columns to the right. I want to take all this data which goes all the way up until column BZ, and move it all into columns A and B so I can do a simple VLOOKUP. I'll separate each data set with the period in between and column A.

What I'm doing right now is starting at cell A1, pressing Ctrl + right arrow four times to get me to the first data set, then pasting it and cell A2. Then going to the last cell in column A that has data, going to the next row, and putting a period there. That concludes the first set of data moved into column A. Now I need to go to the next set of data so I'll go to the cell A1. To get to the first data set I pressed control plus right button for times. This time I'll do that five times. Then same drill I'll move it to column A. Then I'll go back to column A1 and press the Ctrl and right button six times. And so on and so on. That's what you're seeing in the data below.

How would I clean up this code so I can simply loop it rather than writing essentially the same instructions 10 times?


This is what one iteration of the code would look like.

Range("A1").Select

'This is navigating to the relevant data

Selection.End(xlToRight).Select

Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select


' This is copying the data without headers. Writing "1st Data Set" just for testing / debugging, will remove later
ActiveCell.Offset(1).Select
ActiveCell.Value = "1st Data Set"

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
Application.CutCopyMode = False

'Putting a dot at the end simply to show the end of the data set. Will add more datasets after the dot in subsequent loops.

ActiveCell.FormulaR1C1 = "."

Here's the full code

Sub TestingRearrange2()
'
' TestingRearrange2 Macro
'

' Going to first set of data

Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
' Range("M2").Select

' This should move selection one cell down
ActiveCell.Offset(1).Select
ActiveCell.Value = "1st Data Set"

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A2").Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
'Range("A17").Select

' This should move selection one cell down
ActiveCell.Offset(1).Select

Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "."

Range("A1").Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select

' This should move selection one cell down
ActiveCell.Offset(1).Select
ActiveCell.Value = "2nd Data Set"

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.End(xlDown).Select
'Range("A18").Select
ActiveCell.Offset(1).Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
' Range("A35").Select (idk what this is)
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "."
Range("A1").Select
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select

'Range("S2").Select
ActiveCell.Offset(1).Select
ActiveCell.Value = "3rd Data Set"

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.End(xlDown).Select
'Range("A36").Select
ActiveCell.Offset(1).Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
' Range("A51").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "."
Range("A1").Select
Selection.End(xlUp).Select

Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select

'Range("V2").Select
ActiveCell.Offset(1).Select
ActiveCell.Value = "4th Data Set"

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "."
Range("A1").Select
Selection.End(xlUp).Select

Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select

ActiveCell.Offset(1).Select
ActiveCell.Value = "5th Data Set"

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "."
Range("A1").Select
Selection.End(xlUp).Select

'Remember to add a row
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select

ActiveCell.Offset(1).Select
'Remember to increment
ActiveCell.Value = "6th Data Set"

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "."
Range("A1").Select
Selection.End(xlUp).Select
End Sub

 

[Fluff]

Duplicate to: how do i take this loong code and turn it into a loop?

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.