Hi! I have been working on a macro that looks at a table on another worksheet, and selects certain cells based on the criteria provided in the code. I would like for the information to then be pasted onto a new worksheet, however I cannot find a way to write the code that will start on row 92 and list it on down until the identified selections have been all listed. Anything online is using the END.(xlsup) funtion which puts it at the end of all the data on the worksheet (which again will not work as there is data that continues after the area I would like this information posted to). I tried a for loop, but then that just repeated the next value found in the orginial list several times... I am not sure what to try next :S thanks for the help in advance!
Below is the code:
Sub fortyfoot()
Dim wb As Worksheet: Set wb = ActiveWorkbook.Sheets("Current Loads")
Dim wb2 As Worksheet: Set wb2 = ActiveWorkbook.Sheets("Plant Overview")
Dim rCell As Range
Application.ScreenUpdating = False
Worksheets("Plant Overview").Range("b92:d103").ClearContents
For Each rCell In wb.Range("B1:B" & wb.Range("B" & Rows.Count).End(xlUp).Row)
If rCell.Value = wb2.Range("$C$41") And rCell.Offset(0, 2).Value = "40'" And rCell.Offset(0, 3).Value = "EMPTY" And rCell.Offset(0, 13).Value > 5 Then
wb.Range("C" & rCell.Row).Copy
If wb2.Range("b92").Value = "" Then
wb2.Range("b92").PasteSpecial xlPasteValues
Else
For i = 93 To 103
wb2.Cells(i, 2).PasteSpecial xlPasteValues
Next i
End If
End If
If rCell.Value = wb2.Range("$C$41") And rCell.Offset(0, 2).Value = "40'" And rCell.Offset(0, 3).Value = "EMPTY" And rCell.Offset(0, 13).Value > 5 Then
wb.Range("O" & rCell.Row).Copy
If wb2.Range("d92").Value = "" Then
wb2.Range("d92").PasteSpecial xlPasteValues
Else
For i = 93 To 103
wb2.Cells(i, 4).PasteSpecial xlPasteValues
Next i
End If
End If
Next rCell
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Bookmarks