You can't use the record function to create a macro of this complexity. You
need to write it in the Visual Basic Environment.

You can certainly work down the cells in a sheet and look to see if they are
empty. The name of the worksheet a cell is in can be gained from the Worksheet
property of the cell, if you don't already have it in the code.