If statement to change value of next empty cell in range on another sheet

[jchi2210]

Hello guys, I'm trying to wrap my head around this and it's driving me nuts.

I'm trying to setup an If statement so that if it's true, then it places text on a specific range on another sheet on the next empty cell in that range. I tried it with ranges and couldn't figure it out so just to get it working I tried working with just a specific row starting at a specific point and I seem to have broken that as well.

The Range to copy to on Templates is B12:G22. It's currently only changing the value of B12 in the "Template" sheet, which is great that I got that working. But I want to be able to hit the button again and it move to B13. If B13 has a value of any kind in it, then move on. After filling up B22, I was trying to get it to move to C12 and continue on. I know this is kind of over my head and I apologize.

If Sheets("Main").Range("Y4").Value = 2 Then
Sheets("Template").Range("B12").Value = 1
With Sheets("Template").Range("B" & Rows.Count).End(xlUp).Offset(12)
End With
End If

 

[jchi2210]

So are you just after this:

Option Explicit
Sub Macro2()

    Application.ScreenUpdating = False

    If Sheets("Main").Range("Y4").Value = 2 Then
        Sheets("Template").Range("B12:G22").Value = 1
    End If
   
    Application.ScreenUpdating = True

End Sub

Just change the range in second line to suit (the range could even be in a cell that is referenced).

Just what I need. Thank you!