Searching for an empty row in a given destination

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

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.

StackExchange Code Review Q#29450, answer score: 12