Multiple conditions multiple worksheets VBA Closed

Question

Hello, 

I've a small macro which I cannot get working. I would like the macro to copy from sheet 1 anything that has the country code 'ITA' and is greater than 0.185 Place into a sheet called Italy, then repeat the above and place the results in 'Italy' under the previously captured results. The macro that I have keeps overriding sheet 1 results with sheet 2

Sub CopyCountries()
shts = Array("Sheet 1", "Sheet 2")
For sh = LBound(shts) To UBound(shts) 'cycle thru sheets
    With Sheets(shts(sh))
        lr = .Cells(.Rows.Count, "G").End(xlUp).Row 'last row in G
        f = 2 'row counters for country sheets
        i = 2
        s = 2
        p = 2
        For r = 2 To lr  'cycle thru all rows
            Select Case UCase(.Cells(r, "G")) 'get G column value
                Case "ITA"
                    If .Cells(r, "C") <= -0.185 Then
                        .Range("A" & r).Resize(, 9).copy Destination:=Sheets("Italy").Range("A" & i)
                        i = i + 1
                        f = f + 1
                        End If
                Case Else
            End Select
        Next r
    End With
Next sh
End Sub


Thanks for taking the time to read this 

Configuration: Windows XP / Internet Explorer 7.0

Configuration: Windows XP / Internet Explorer 7.0

TrowaD · Answer

Hi Np72,

Based on the line: 
"I would like the macro to copy from sheet 1 anything that has the country code 'ITA' and is greater than 0.185 Place into a sheet called Italy" 
I have written the following code:
Sub test()
Dim lRow, lRow2 As Integer
Dim cell As Range
lRow = Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
For Each cell In Range("G2:G" & lRow)
    If cell.Value = "ITA" And cell.Offset(0, -4).Value > 0.185 Then
        lRow2 = Sheets("Italy").Range("G" & Rows.Count).End(xlUp).Row
        cell.EntireRow.Copy Destination:=Sheets("Italy").Range("A" & lRow2 + 1)
    End If
Next cell
End Sub
Then you say:
"then repeat the above and place the results in 'Italy' under the previously captured results"
This is done by running the code again.

But it's not clear to me what you want to do with sheet2. Ignore it?, search it as well?

Best regards,
Trowa

Multiple conditions multiple worksheets VBA

1 response

Similar discussions

Subscribe To Our Newsletter!