PDA

View Full Version : [SOLVED] Help w/combining excel actions



Denblanc
03-01-2016, 07:36 AM
Hello,
Any assistance anyone can give will be appreciated, still learning VBA.

I have excel sheet that displays Columns "D, E, AD, AE, AF" with unknown variable of rows. Need to copy rows based on Country array to new sheet that is also created.

CODE
Sub MakeCopy()

Dim Row As Range
Dim FirstFind As Range
Dim rng As Range
Dim sCountry As String
Dim sFind As String
Dim ws As Worksheet
Dim wsTempo As Worksheet

On Error Resume Next
Set wsSheet1 = ThisWorkbook.Worksheets("Sheet1")
If Err = 9 Then
Set wsSheet1 = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets.Count)
wsTempo.Name = "Tempo"
End If
On Error GoTo 0

sFind = Array("Afghanistan", "Algeria", "Angola", "Azerbaijan", "Bangladesh", "Brazil", "Burkina Faso", "Burundi", _
"Cambodia", "Cameroon", "Central African Republic", "Chad", "Chechnya", "Colombia", "Congo", "Congo DRC", _
"Cote d'lvoire", "Ecuador", "Egypt", "El Salvador", "Eritrea", "Ethiopia", "Georgia", "Guatemala", _
"Guinea-Bissau", "Haiti", "Honduras", "India", "Indonesia", "Iran", "Iraq", "Israel", "Jamaica", "Kenya", _
"Kuwait", "Kyrgyzstan", "Lebanon", "Libya", "Madagascar", "Mali", "Mauritania", "Mexico", "Myanmar", _
"Nepal", "Niger", "Nigeria", "Pakistan", "Palestinian Territories", "Panama", "Papua New Guinea", _
"Peru", "Philippines", "Qatar", "Russia", "Saudi Arabia", "Somalia", "South Africa", "South Sudan", _
"Sudan", "Syria", "Tajikistan", "Thailand", "Trinidad and Tobago", "Uganda", "Ukraine", _
"United Arab Emirates", "Uzbekistan", "Venezuela", "Yemen")

For Each ws In Worksheets
If ws.Name Like "Sheet1" Then
Set Cell = ws.Cells.Find(what:=sFind, lookat:=xlPart, LookIn:=xlFormulas)
If Not Cell Is Nothing Then
Set FirstFind = Cell
Do
Row = Row + 1
Set rng = ws.Range(Cell.Offset(0, 1), Cell.End(xlToRight))
rng.Copy Destination:=wsTempo.Cells(Row, "A")
Set Cell = ws.Cells.FindNext(Cell)
If Cell.Country = FirstFind.Country Then Exit Do
Loop
End If
End If
Next ws

End Sub
CODE