Find next empty cell in column.

[Guest]

I need a macro to
Copy the contents of the active cell on Sheet1 to the next empty cell in
column"A" on Sheet2,
Wait for the user to enter a number in next column on the same row,
Then return to the active cell on Sheet1

Your suggestions are greatly appreciated.

 

[Guest]

hi
something like this might work. example code only...

Sub transfertest()
Dim ip As String
Dim sh As Worksheet
Dim rng As Range
Set sh = ActiveSheet
Set rng = ActiveCell
ActiveCell.Copy
Sheets("sheet2").Activate
Range("a1").End(xlDown).Offset(1, 0).PasteSpecial xlPasteAll
ip = InputBox("enter something") 'change if needed
ActiveCell.Offset(0, 1).Value = ip
If MsgBox("do you want to go back to the last sheet?", vbYesNo) = vbYes Then
sh.Select
rng.Select
Else
Exit Sub
End If
End Sub

regards
FSt1

 

[Guest]

the bestt way of doing this is to setup some shortcut keys that activate
simple macros like the one below

Sub lastrow()

Sheets("Sheet2").Activate
mlastrow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(mlastrow + 1, "A").Activate

End Sub

The above macro will move to the first empty cell in column a on worksheet
2. You can assign the macro a shortcut key by going to the worksheet menu
Tools - Macro - Options. Assign a key. This will get you to sheet 2. Going
back to sheet 1 just requires pressing the sheet 1 Tab on the bottom of the
worksheet.

 

[Guest]

Thanks very much. I combined your code with Joel's and it works great.

 

[Guest]

Not sure if you are using msgbox like FST1 had in his code. MSGBOX only
deals with text data. Some items like a date will get converted to text and
stay text if it is put into a MSGBOX. This may not be what you want.

 

[Guest]

In my case InputBox is asking for a numeric value so the code works well.
I chose your method of finding the next row because there may be empty cells
above the last row.
Thanks again for the help!