Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find first empty cell in column | Excel Discussion (Misc queries) | |||
Find first empty cell in column | Excel Discussion (Misc queries) | |||
find next NOT empty cell in column | Excel Programming | |||
Find a empty cell in next column | Excel Discussion (Misc queries) | |||
How to find next empty cell within a column? | Excel Programming |