rhudgins
04-21-2010, 12:15 PM
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.
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
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.
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