insert into next cell if occupied

Paste this into your source worksheet module (to get there, right click on the sheet tab, left click on View Code):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = [A1] Then
Run "CopyAtoB"
End If
End Sub

Then, in a standard VBA module, paste in this macro:

Sub CopyAtoB()
Sheet1.[A1].Copy _
Sheet2.[B5].End(xlDown).Offset(1, 0)
End Sub

Modify the worksheets' VBA object reference, depending on what sheets are involved.

HTH

Tom Urtis

One modification

The "CopyAtoB" macro would work better like this, modify the "begin up from" row as needed...I placed 65536 in here but you can make it whatever you want.

Sub CopyAtoB()
Sheet1.[A1].Copy _
Sheet2.[B65536].End(xlUp).Offset(1, 0)
End Sub

Tom Urtis

Thank you very much for your help.
Unfortunately, I am not very familiar with VB and how it works, but I'll see if I can figure how to use your script.

Thanks again,
Mike

Re: One modification

Tom
Thanks again for your help, I got it working.
I have another question though.
How would I then reverse this equation?
Now that cells b5, b6, b7 etc.. have been filled out on sheet2, how can I get these values to show back into cell d20 only on sheet1?
So as a1 is filled out on sheet1 it fills in cell b5 on sheet2 if this cell is empty else it goes to b6 etc... But now I would like cell b5 or b6 etc... to appear in cell d20 on sheet1.

Again I would appreciate any help.

Mike

Re: One modification

To keep it simple, how about keeping the macros as they are, and in cell D20 enter the formula "=A1" (without the quotes), which should give you what you want.

Tom Urtis

Re: One modification

That would work, but in cell b5, b6 etc... I have added information to it (added text to the cell), and I need this new info brought back.

Thanks,
Mike To keep it simple, how about keeping the macros as they are, and in cell D20 enter the formula "=A1" (without the quotes), which should give you what you want.

Re: One modification

I'm not clear on what you want. What is it you want in D20 of Sheet1? If it is whatever is in B5 or B6 of Sheet2, then that will be exactly what gets placed in cell A1 of Sheet1. If you are saying that later, after entering a number in A1, that you go into cell B5 or whatever in Sheet2, and make a change in that cell, and want that newly edited value to be in D20 of Sheet1, then we can place a formula or macro to display that edited value into D20. But then you say you have added information to B5 or B6 "to the next cell", and I don't know what that "next cell" is (C5 or C6, or B6 or B7).

Can you please provide more info on exactly what you are doing.

Tom U.

That would work, but in cell b5, b6 etc... I have added information to it (added text to the cell), and I need this new info brought back. Thanks,

Re: One modification

Sorry for being slightly unclear, but what I was originally trying to do had to be modified.
Here is what I am actually trying to accomplish:
I would like to enter a name in cell A1 Sheet1.
I would like this information to appear in cell A5 Sheet2 and beside it in cell B5 Sheet2 a specific number.
I would then like to combine these 2 cells and have this information show back into cell D20 Sheet1.
I also would like to be able to keep entering a name in cell A1 Sheet1, and have this information appear in cell A6 Sheet2 and for cell B6 Sheet2 to increment by 1, and so on etc... and for it to show up in cell D20 Sheet1.

Sheet1
A D
1 Bob

20 Your id# is: Bob 1200
20 Your id# is: Mary 1201 etc...

Sheet2
A B
4 Name Number
5 Bob 1200
6 Mary 1201
7 John 1202 etc...

Mike Kuwahara

Question

What do you mean by "specific number" in cell B5 of Sheet2, but then on B6 to "increment that value by 1". Does that mean that B6 is not a specific number, such as an employee ID number, but only a counting number? Should the macro add a value of 1 in B6 etc of Sheet2, or is that "specific number" an employee ID number generated by a Lookup or IndexMatch formula?

T.U.

Sorry for being slightly unclear, but what I was originally trying to do had to be modified.

Re: Question

The 'specific number' is a counting number, but originally defined by me.
Yes, the macro should add a value of 1 in B6 etc.. of Sheet2.

Mike What do you mean by "specific number" in cell B5 of Sheet2, but then on B6 to "increment that value by 1". Does that mean that B6 is not a specific number, such as an employee ID number, but only a counting number? Should the macro add a value of 1 in B6 etc of Sheet2, or is that "specific number" an employee ID number generated by a Lookup or IndexMatch formula? T.U.

Answer (?)

Replace the original "CopyAtoB" macro with this one:

Sub CopyAtoB()
Sheet1.[A1].Copy _
Sheet2.[A65536].End(xlUp).Offset(1, 0)
If Sheet2.[A65536].End(xlUp).Offset(1, 0).Row < 6 Then Exit Sub
Sheet2.[A65536].End(xlUp).Offset(0, 1).Value = Sheet2.[A65536].End(xlUp).Offset(-1, 1).Value + 1
Sheet1.[D20].Value = Sheet2.[A65536].End(xlUp).Value & Sheet2.[B65536].End(xlUp).Value
End Sub

It worked when I tested it, hope it's what you want.

Tom Urtis

Thanks a lot!!

Tom,
I really want to thank you for your help. It works great.

Mike