1 Attachment(s)
Adjusting macro to loop through criteria
I would like to run the following macro in a loop for the five criteria listed in cells AE3:AG7. Right now it is only running the criteria for Example #1. Example #1 pastes the final data into cells F8:I22.
I would like example #2 to paste the final data into cell K8:N22.
I would like example #3 to paste the final data into cell P8:S22
I would like example #4 to paste the final data into cell U8:X22
I would like example #5 to paste the final data into cell Z8:AC22
Here is my macro. The macro is also in the attached spreadsheet.
Code:
Public CurRow As Long
Public Code As Long
Sub RunResults()
Sheets("Chopped Up Long Data Close").Select
RunIt
End Sub
Private Sub RunIt()
Range("AE3").Select
Code = ActiveCell.Value
If (Code > 0) Then
getreport (Code)
End If
End Sub
Private Sub getreport(Code As Integer)
'First line
Dim Rng As Range, MyCell As Range, i As Long
Set Rng = Range("A25:A66")
i = 102
For Each MyCell In Rng
If MyCell.Offset(0, 3).Value = Range("AF3") And MyCell.Offset(0, 4).Value = Range("AG3") Then
MyCell.EntireRow.Copy
Range("A" & i).PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
i = i + 1
End If
Next MyCell
Application.CutCopyMode = False
Range("A8:D22").Select
Selection.Copy
Range("F8:I22").Select
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cleanup
End Sub
Private Sub Cleanup()
Sheets("Chopped Up Long Data Close").Select
Range("A102:IU131").Select
Selection.ClearContents
End Sub