7
\$\begingroup\$

It simply searches for an empty row in a given destination. If it is empty, it performs certain actions (copying, pasting, etc.). If it is not empty, it goes to another row. I managed to do it with dozens of If formulas, but doing it in such a way seems really ineffective. Moreover, doing it in such a way puts a severe limitation on the number of Ifs I can put in my code.

My code is too long and cannot be executed. Is there a way to ameliorate my code somehow?

Sub My_Macro

Application.ScreenUpdating = False

Dim Worksheet As Worksheets
startrow = Worksheets("GUTS").Cells(10, 1) 'Here I denote value 1
endrow = Worksheets("GUTS").Cells(11, 1) 'Here I denote value 1000

For x = startrow To endrow
    If Cells(x, "O").Value = "P" Or Cells(x, "O").Value = "R" Then

        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value, "C") = "" Then
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 1, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 1, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 1, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 1, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 1, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 2, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 2, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 2, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 2, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 2, "C") = "Pur"""

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 3, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 3, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 3, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 3, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 3, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 5, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 5, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 5, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 5, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 5, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 6, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 6, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 6, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 6, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 6, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 7, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 7, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 7, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 7, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 7, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 8, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 8, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 8, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 8, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 8, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 9, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 9, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 9, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 9, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 9, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 10, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 10, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 10, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 10, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 10, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 11, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 11, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 11, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 11, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 11, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 12, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 12, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 12, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 12, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 12, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 13, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 13, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 13, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 13, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 13, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 14, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 14, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 14, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 14, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 14, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 15, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 15, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 15, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 15, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 15, "C") = "Pur"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 16, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 16, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 16, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 16, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 16, "C") = "Pur"

        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
Else
If Cells(x, "O").Value = "S" Then

        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value, "C") = "" Then
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 1, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 1, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 1, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 1, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 1, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 1, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 2, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 2, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 2, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 2, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 2, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 2, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 3, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 3, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 3, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 3, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 3, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 3, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 1, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 5, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 5, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 5, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 5, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 5, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 5, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 6, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 6, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 6, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 6, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 6, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 6, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 7, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 7, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 7, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 7, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 7, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 7, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 8, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 8, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 8, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 8, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 8, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 8, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 9, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 9, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 9, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 9, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 9, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 9, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 10, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 10, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 10, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 10, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 10, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 10, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 11, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 11, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 11, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 11, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 11, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 11, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 12, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 12, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 12, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 12, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 12, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 12, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 13, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 13, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 13, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 13, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 13, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 13, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 14, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 14, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 14, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 14, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 14, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 14, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 15, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 15, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 15, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 15, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 15, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 15, "C") = "Sale"

    Else
        If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 16, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 16, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "G" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 16, "E").PasteSpecial xlPasteAll
        Range("I" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 16, "H").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 16, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 16, "C") = "Sale"

        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
Else
    If Cells(x, "O").Value = "C" Or Cells(x, "O").Value = "N" Then
            Sheets("Manual processing").Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Range("P" & x).Copy
            Sheets("Manual processing").Range("A3").PasteSpecial Paste:=xlPasteValues
            Range("C" & x, "O" & x).Copy
            Sheets("Manual processing").Range("B3").PasteSpecial Paste:=xlPasteValues
            Range("Q" & x).Copy
            Sheets("Manual processing").Range("O3").PasteSpecial Paste:=xlPasteValues

            End If
        End If
End If
Next    

Application.ScreenUpdating = True

End Sub
\$\endgroup\$

2 Answers 2

Reset to default
8
\$\begingroup\$

As suggested by Brian, you should try a for..next

Something like

Dim i As Integer
For i = 1 To 16
    If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + i, "C") = "" Then
        With Worksheets(Cells(x, "P").Value)
            .Cells(Cells(x, "Q").Value + i, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Range("F" & x, "H" & x).Copy
            .Cells(Cells(x, "Q").Value + i, "E").PasteSpecial xlPasteAll
            Range("J" & x).Copy
            .Cells(Cells(x, "Q").Value + i, "J").PasteSpecial xlPasteAll
            .Cells(Cells(x, "Q").Value + i, "C") = "Pur"
        End With
    End If
Next i

And yet, several suggestions :

  • use With statement when dealing with the same object on several lines
  • be careful with your Range and Cells statements because you don't seem to know where you are working on. You'd better double-check by almost always using Worksheets("Sheet1").Range("A1")
\$\endgroup\$
0
12
\$\begingroup\$ 
 If Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "C") = "" Then
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "A").Resize(, 10).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("F" & x, "H" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "E").PasteSpecial xlPasteAll
        Range("J" & x).Copy
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "J").PasteSpecial xlPasteAll
        Worksheets(Cells(x, "P").Value).Cells(Cells(x, "Q").Value + 4, "C") = "Pur"

Copy-pasting a snippet over and over again is a hint that you need a loop or a function.

The only difference between each snippet being that the 4 was replaced with different numbers. You could replace this code repetition with a loop (instead of 4, use the loop index). Alternatively, you could replace this code snippet with a function which takes a number, then call this function over and over again, passing it that number (which will replace the 4). You could even do both.

Either way, the first step in making this code readable is probably to avoid repeating the same code over and over again.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.