We have a workbook where data from completed invoices is copied into column C of Sheet1 every month.
Column A is for the Year and Column B is for the financial period (we run 13 periods per year)

Currently this formula is on column B of sheet 1, it checks to see if column C has anything in it and if so displays the current period from a cell in Sheet2
=IF(C93<>””,Sheet2!$L$3,””)

As you can imagine this isn’t elegant and requires the cells to have the period information copied and re-pasted as values before the period changes over to the next one.

For this latest version on sheet2 we have a table with the 13 periods in column Q and column P indicates if that period is closed
I found the below code online and adapted it for my needs, it finds the first empty cell in Column B of Sheet1 and then checks Sheet2 and pastes the value of the cell in Column Q that has an empty cell beside it in Column P.

Sub GetPeriod()
Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, x As Long
x = Range(“A2:A” & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Set srcWS = Sheets(“Sheet2”)
Set desWS = Sheets(“Sheet1”)
x = srcWS.Range(“P2:P” & srcWS.Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
With desWS
.Cells(.Rows.Count, “B”).End(xlUp).Offset(1) = srcWS.Range(“Q” & x)
End With
End Sub

It works well enough for a beginners attempts, however I lack the knowledge or understanding of VBA to make it loop and then stop when it reaches the last of the pasted data in column C of Sheet1

And this is where I’m hoping the forum could come to my rescue and help me out with the loop and stop commands as Ive hit a wall and cant seem to get my head around it

For this latest version on sheet2 we have a table with the 13 periods in column Q and column P indicates if that period is closed
I found the below code online and adapted it for my needs, it finds the first empty cell in Column B of Sheet1 and then checks Sheet2 and pastes the value of the cell in Column Q that has an empty cell beside it in Column P.

Sub GetPeriod()
Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, x As Long
x = Range(“A2:A” & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Set srcWS = Sheets(“Sheet2”)
Set desWS = Sheets(“Sheet1”)
x = srcWS.Range(“P2:P” & srcWS.Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
With desWS
.Cells(.Rows.Count, “B”).End(xlUp).Offset(1) = srcWS.Range(“Q” & x)
End With
End Sub

It works well enough for a beginners attempts, however I lack the knowledge or understanding of VBA to make it loop and then stop when it reaches the last of the pasted data in column C of Sheet1

And this is where I’m hoping the forum could come to my rescue and help me out with the loop and stop commands as Ive hit a wall and cant seem to get my head around it.