VBA Variable Row Selected In Wrong Sheet

[AcornNut]

Hello all,

I'm trying to populate an order form ("Supply Usage Form") using VBA. I have a command button that, when selected, loops through a location-based inventory sheet ("Location") and checks columns L & M for missing or expiring items. If any are found, it auto-populates the order form. Before moving onto the next row, it loops through the order form looking for duplicate items in column B. If it finds any, it goes to the first time that item is listed and then tally's the quantities in column H. Then it's supposed to select the duplicate row and clear it. However, every time it reaches this line of code, Rows(x).EntireRow.ClearContents", it selects the corresponding row in the inventory sheet, not the order form. How can I get it to stay on the order form? I've tried declaring my rows as "integer" and as "long", and every combination I can think of.

Thanks in advance!!!!!!

Here's my code...

Sub fillorder1()
   Dim finalrow As Long
   Dim i As Integer
   Dim lastrow As Long
   Dim NextRow As Long
   Dim Flg As Boolean
   Dim FirstRow As Long
   Dim x As Long
   Dim y As Long
   Dim LR1 As Long

   Application.ScreenUpdating = False
   LR1 = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
   NextRow = 54
   lastrow = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row + 1
   finalrow = Sheets("Location").Range("B9").End(xlDown).Row

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
                LR1 = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
                For x = LR1 To 24 Step -1
                    For y = 24 To LR1
                        If Sheets("Supply Usage Form").Cells(y, 4).Value = Sheets("Supply Usage Form").Cells(x, 4).Value And x > y Then
                            Sheets("Supply Usage Form").Cells(y, 8).Value = Sheets("Location").Cells(i, 2).Value + Sheets("Supply Usage Form").Cells(x, 8).Value
                            Rows(x).EntireRow.ClearContents
                        Exit For
                        End If
                    Next y
                Next x


        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
            LR1 = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
                For x = LR1 To 24 Step -1
                    For y = 24 To LR1
                        If Sheets("Supply Usage Form").Cells(y, 4).Value = Sheets("Supply Usage Form").Cells(x, 4).Value And x > y Then
                            Sheets("Supply Usage Form").Cells(y, 9).Value = Sheets("Location").Cells(i, 2).Value + Sheets("Supply Usage Form").Cells(x, 9).Value
                            Rows(x).EntireRow.Select
                        Exit For
                        End If
                    Next y
                Next x
                
        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
            LR1 = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
                For x = LR1 To 24 Step -1
                    For y = 24 To LR1
                        If Sheets("Supply Usage Form").Cells(y, 4).Value = Sheets("Supply Usage Form").Cells(x, 4).Value And x > y Then
                            Sheets("Supply Usage Form").Cells(y, 8).Value = Sheets("Location").Cells(i, 2).Value + Sheets("Supply Usage Form").Cells(x, 8).Value
                            Sheets("Supply Usage Form").Cells(y, 9).Value = Sheets("Location").Cells(i, 2).Value + Sheets("Supply Usage Form").Cells(x, 9).Value
                            Rows(x).EntireRow.ClearContents
                        Exit For
                        End If
                    Next y
                Next x
        End If
    Next i
End Sub

 

[AcornNut]

Thanks. I replied to Mark838's comment that explains what I think happened to get the error (multiple windows open and a different window was selected prior to running the code, so it looked to the wrong sheet). Just a guess.

I'd love to streamline the code, but I'm learning on the fly with trial and error. I barely understand what I'm doing and easily get confused. I've thought about With statements, and tried them a couple times, but could never get them to work.
If you have any suggested amendments, I'd be more than happy to try them.

 

[JLGWhiz]

Thanks. I replied to Mark838's comment that explains what I think happened to get the error (multiple windows open and a different window was selected prior to running the code, so it looked to the wrong sheet). Just a guess.

I'd love to streamline the code, but I'm learning on the fly with trial and error. I barely understand what I'm doing and easily get confused. I've thought about With statements, and tried them a couple times, but could never get them to work.
If you have any suggested amendments, I'd be more than happy to try them.

I wouldn't want to attempt to re-write the code without being able to see the worksheets and understand what your loops are trying to do with the data. I was looking at the boolean 'Flg' and wondering why it is needed since it is basically used in only one place to test the value. But without knowing what you start with and what you are trying to end up with, I could screw up your working code. So, I will pass on that.