The task of putting the information into the right column  is only a small part
(and potentially the easiest part) of this exercise. The harder part is
deciding how to run the code  and how to detect the extents of the data  areas.

From the way you describe it, this sounds like a commercial business, and I
think you are probably well out of your depth with the programming for it. I'd
recommend that you get the people who do your company's programming to do this
one, or retain the services of a local contractor.

On the other hand, it would be feasible to put an if statement into each cell  in
the rectangular area that is the leases and the months. This if could look in
the cell in row  1 (using $ to anchor on the row) to find the month/year the
column represents and check this against the month/year from the lease expiry
date (using $ to anchor on the column) and do a VLookup from the second sheet  if
the lease was expiring in that month.

If you use the $ anchors appropriately, it is possible to copy/paste a single
formula to all cells in the rectangular area. You would need to create extra
rows and fill them yourself as new leases start.