Macro for infinitely moving data to new location (need help).

[Wheatly]

Hi, so I have a list of countries and their populations. I have been able to macro 3 lines thus far through excel. My question is, is there a way to repeat this process for the remaining Countries? I’ve attached my excel and included my macro code below.
'
' Macro2actual Macro
'

'
Range("A2").Select
Selection.Copy
Range("H2").Select
ActiveSheet.Paste
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Range("I2").Select
ActiveSheet.Paste
Range("A5").Select
Application.CutCopyMode = False
Selection.Copy
Range("H3").Select
ActiveSheet.Paste
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Range("I3").Select
ActiveSheet.Paste
Range("A8").Select
Application.CutCopyMode = False
Selection.Copy
Range("H4").Select
ActiveSheet.Paste
Range("B9").Select
Application.CutCopyMode = False
Selection.Copy
Range("I4").Select
ActiveSheet.Paste
End Sub
So how can I edit this code to continue on this rule for remaining Countries? Thanks in advance!

[mana]

Option Explicit

Sub test()
Dim n As Long
Dim i As Long

n = 2
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row Step 3
Cells(n, "H").Value = Cells(i, "A").Value
Cells(n, "I").Value = Cells(i + 1, "B").Value
n = n + 1
Next

End Sub

[rlv]

Sub test2()
Dim i As Long
Dim WS As Worksheet
Dim R As Range

Set WS = ActiveSheet
i = 1
For Each R In WS.Range("B2:B21")
If Trim(R.Text) <> "" Then
R.Offset(-1, -1).Copy WS.Range("H1").Offset(i) 'country
R.Copy WS.Range("I1").Offset(i) 'population
i = i + 1
End If
Next R
End Sub

[p45cal]

Sub blah()
With Columns("A:A")
.AutoFilter Field:=1, Criteria1:="~"
Set Rng = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
.AutoFilter
End With
Rng.Offset(1).Copy Range("H2")
Rng.Offset(2, 1).Copy Range("I2")
End Sub

[Wheatly]

Hey p45cal,

How would I go about macroing the attached excel data? This is from a different source so the information is in different areas (Population and Country). The Countries and population was macroed into H2, I2 for previous table, how would you adjust this code to place the new data in J2 and K2?

Thanks for the help p45cal and everyone else. I appreciate it! :)

[p45cal]

This might work if the population figures are always 5 cells below the cells containing 'COUNTRY':
Sub blah2()
With Columns("A:A")
.AutoFilter Field:=1, Criteria1:="COUNTRY"
Set Rng = Intersect(ActiveSheet.AutoFilter.Range, ActiveSheet.AutoFilter.Range.Offset(1)).SpecialCells(xlCellTypeVisible)
.AutoFilter
End With
Rng.Offset(1).Copy Range("J2")
Rng.Offset(5).Copy Range("K2")
End Sub



How would I go about macroing the Any noun can be verbed…

[Wheatly]

I just tried it then and it worked! Thanks heaps!!