I have a code that searches Sheet1 ("Location") for certain criteria and populates Sheet2 ("Supply Usage Form") with that data. This works fine, after looping through all the rows on Sheet1, I need the code to loop through all the newly added data in Sheet2 for duplicates, combine the rows and sum the values for each identical item. The first loop works perfectly on its own.
The problem is... if Sheet2 is blank, then neither code works and it continually compiles and never completes. If there's data already in Sheet2, then when the code is run it repeats the first loop, but doesn't combine anything, so I have duplicates, but when you run it again the second loop will combine and sum the values, but moves the original data down, leaving a number of blank rows between the newly combined rows and the existing data. And continues to do so indefinitely with each new run of the code.
What I need is, whether or not Sheet2 is blank or has data already, the code to add the data from Sheet1 and the check Sheet2 for duplicates and, if it finds any, combine the rows and sum the values in the appropriate columns, then clear the original data.
Any help is greatly appreciated! Thank you in advance!!!
The problem is... if Sheet2 is blank, then neither code works and it continually compiles and never completes. If there's data already in Sheet2, then when the code is run it repeats the first loop, but doesn't combine anything, so I have duplicates, but when you run it again the second loop will combine and sum the values, but moves the original data down, leaving a number of blank rows between the newly combined rows and the existing data. And continues to do so indefinitely with each new run of the code.
What I need is, whether or not Sheet2 is blank or has data already, the code to add the data from Sheet1 and the check Sheet2 for duplicates and, if it finds any, combine the rows and sum the values in the appropriate columns, then clear the original data.
Any help is greatly appreciated! Thank you in advance!!!
Code:
Sub fillorder1()
Dim finalrow As Long
Dim i As Integer
Dim ExpDate As Date
Dim lastrow As Long, NextRow As Long
Dim Flg As Boolean
Dim LR As Long
Application.ScreenUpdating = False
Number_1 = 1
Number_2 = Cells(6, 4)
LR = Sheets("Supply Usage Form").Range("B24").End(xlDown).Row
NextRow = 54
lastrow = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row + 1
finalrow = Sheets("Location").Range("B9").End(xlDown).Row
ExpDate = Date + 30
Flg = True
For i = 9 To finalrow
If Flg And lastrow > 53 Then
Rows(NextRow).Insert
Rows(53).Copy
Rows(NextRow).PasteSpecial xlFormats
lastrow = NextRow
NextRow = NextRow + 1
End If
Flg = False
If (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) = "") Then
Sheets("Location").Cells(i, 1).Copy
Sheets("Supply Usage Form").Range("B" & lastrow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 2).Copy
Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 12).Copy
Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
lastrow = lastrow + 1
Flg = True
ElseIf (Sheets("Location").Cells(i, 12) = "") And (Sheets("Location").Cells(i, 13) <> "") Then
Sheets("Location").Cells(i, 1).Copy
Sheets("Supply Usage Form").Range("B" & lastrow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 2).Copy
Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 13).Copy
Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
lastrow = lastrow + 1
Flg = True
ElseIf (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) <> "") Then
Sheets("Location").Cells(i, 1).Copy
Sheets("Supply Usage Form").Range("B" & lastrow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 2).Copy
Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 12).Copy
Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
Sheets("Location").Cells(i, 13).Copy
Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
lastrow = lastrow + 1
Flg = True
End If
Next i
Flg = True
For x = LR To 24 Step -1
For y = 24 To LR
If Sheets("Supply Usage Form").Cells(x, 2).Value = Sheets("Supply Usage Form").Cells(y, 2).Value And Sheets("Supply Usage Form").Cells(x, 3).Value = Sheets("Supply Usage Form").Cells(y, 3).Value And x > y Then
Sheets("Supply Usage Form").Cells(y, 8).Value = Sheets("Supply Usage Form").Cells(x, 8).Value + Cells(y, 8).Value
Sheets("Supply Usage Form").Cells(y, 9).Value = Sheets("Supply Usage Form").Cells(x, 9).Value + Cells(y, 9).Value
Sheets("Supply Usage Form").Cells(y, 10).Value = Sheets("Supply Usage Form").Cells(x, 10).Value + Cells(y, 10).Value
Sheets("Supply Usage Form").Cells(y, 12).Value = Sheets("Supply Usage Form").Cells(x, 12).Value + Cells(y, 12).Value
Rows(x).EntireRow.ClearContents
Exit For
End If
Next y
Next x
Flg = True
lastrow = Sheets("Supply Usage Form").Range("B24").End(xlDown).Row + 1
If Sheets("Supply Usage Form").Range("B" & lastrow).Row > 53 Then
Rows(lastrow).Delete
End If
If Range("C9") = "" Then
UserForm3.Show
End If
If Range("F9") = "" Then
UserForm4.Show
End If
Sheets("Supply Usage Form").Cells(6, 4).Value = Number_2 + Number_1
Application.ScreenUpdating = True
End Sub