find first empty cell in range and copy data

  • I am looking for a formula function or a vba code where:

    - In workbook1 find the first cell that is empty between range A7 -
    - In workbook2, in Range G10- G13: find the word "Day1".
    - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy
    the particular cell or cells where "Day1" exists to the first found
    empty cell or cells in range A7-A10 in workbook1.

    Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means
    A8 is the first empty cell.
    And G10,G11,G12, G13 (workbook2) have the word "Day1"
    Copy cell G10 into cell A8
    Copy cell G11 into cell A9
    Copy cell G12 into cell A10

    Please let me know if this explanation is confusing.... and I can try
    explaining again.

    Appreciate all help!

  • Re: find first empty cell in range and copy data

    Welcome to Ozgrid!, as i can see from your explanation you want to copy any data found in workbook 2 in this range G10:G13 to the first blank cell in workbook1 this range A7:A10.

    One thing that is clear here is that if in your workbook1 you only have one cell blank lets say A8, when you paste all the information from the range in workbook 2 you will overwrite the rest of the information below A8, is this what you want or do you want to insert the copied cells and move the rest down?, when moving them down do you want to move all rows down or just the cells?

    A little more explanation is needed before we can hazard a guess at your solution!


  • Re: find first empty cell in range and copy data

    This ought to at least get you started. There are more variables here than I originally thought, and I got tired of doing it all. But at least you've got something to work on.


  • Re: find first empty cell in range and copy data

    Hi Simon,

    Yes I want all info below A8 to be over-written. Thank you for youe help!

    Hi Mrkfrrl,

    Thank you for your code, Ill take a look at it and see what I can do with this!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!