Find next empty cell in column.
 

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.

Find next empty cell in column.
 

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

"Dave" wrote:

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.

Find next empty cell in column.
 

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.

"Dave" wrote:

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.

Find next empty cell in column.
 

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

"FSt1" wrote:

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

"Dave" wrote:

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.

Find next empty cell in column.
 

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.

"Dave" wrote:

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

"FSt1" wrote:

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

"Dave" wrote:

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.

Find next empty cell in column.
 

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!


"Joel" wrote:

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.

"Dave" wrote:

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

"FSt1" wrote:

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

"Dave" wrote:

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.