Multiple conditions multiple worksheets VBA [Closed]

Report
Posts
1
Registration date
Thursday December 15, 2011
Status
Member
Last seen
December 15, 2011
-
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
-
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



1 reply

Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!