0

i'm trying to read a list of information from a different sheet on excel, if the the due dates match then all the details for that person are then pasted in to the row on a new sheet.

however, i to try step through all the people on the list but say the person was 4th on the original list then the information for that person would be pasted correctly but 4 places down instead of the top (example shown in picture).

I'm not quite sure on how to fix it so each person that has matching due dates would be copied correctly in order in the right position without being overwritten.

enter image description here

Sub DepotsDue()
Dim i As Long


Worksheets("Depots Due Weekly").Range("A5:F150").ClearContents

For i = 1 To 150


 If Worksheets("Depots Due Weekly").Cells(1, "B").Value = Worksheets("Weekly").Cells(1 + i, "E").Value Then
 Worksheets("Depots Due Weekly").Cells(4 + i, "A").Value = Worksheets("Weekly").Cells(1 + i, "A").Value
 Worksheets("Depots Due Weekly").Cells(4 + i, "B").Value = Worksheets("Weekly").Cells(1 + i, "B").Value
 Worksheets("Depots Due Weekly").Cells(4 + i, "C").Value = Worksheets("Weekly").Cells(1 + i, "C").Value
 Worksheets("Depots Due Weekly").Cells(4 + i, "D").Value = Worksheets("Weekly").Cells(1 + i, "D").Value
 End If

Next i

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
dylan gornall
  • 19
  • 5
  • Not sure I understand. So your picture is desired result? What is current result? – Parfait Apr 17 '20 at 17:19
  • so the picture is the current result. I would like the data if matched correctly to be placed in cell A4 and all the rest of the matched data in the loop to be placed in order below. I know the 4 + i is the issue but im unsure how to make the data appear in order below each other if matched properly. – dylan gornall Apr 17 '20 at 17:25
  • [Find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and add one, instead of `4 + i`. – BigBen Apr 17 '20 at 17:27
  • @Parfait yes sorry that is a separate mistake thanks. – dylan gornall Apr 17 '20 at 17:27
  • @BigBen i will try that now thanks – dylan gornall Apr 17 '20 at 17:27
  • This sounds like a case for `Range.AutoFilter` perhaps, instead of looping. – BigBen Apr 17 '20 at 17:28
  • @Parfait sorry im new to this so im unfamiliar of all the possible methods. – dylan gornall Apr 17 '20 at 17:28
  • hi the reason for clear contents was so that when a new date is selected at the top then data would not overlap and it would only show information for that specific date match – dylan gornall Apr 17 '20 at 17:35

1 Answers1

1

You would havbe to use two counters. One for the original table, one for the new table. Now you step through the original table and whenever you insert a row in the second table, increase the second counter:

Sub DepotsDue()
Dim i As Long
Dim newCounter as long

Worksheets("Depots Due Weekly").Range("A5:F150").ClearContents

For i = 1 To 150

  If Worksheets("Depots Due Weekly").Cells(1, "B").Value = Worksheets("Weekly").Cells(1 + i, "E").Value Then
    Worksheets("Depots Due Weekly").Cells(4 + newCounter , "A").Value = Worksheets("Weekly").Cells(1 + i, "A").Value
    Worksheets("Depots Due Weekly").Cells(4 + newCounter , "B").Value = Worksheets("Weekly").Cells(1 + i, "B").Value
    Worksheets("Depots Due Weekly").Cells(4 + newCounter , "C").Value = Worksheets("Weekly").Cells(1 + i, "C").Value
    Worksheets("Depots Due Weekly").Cells(4 + newCounter , "D").Value = Worksheets("Weekly").Cells(1 + i, "D").Value
    newCounter =newCounter + 1
  End If

Next i

End Sub