Transfer entered data from Sheet1 to Sheet2

[courageoushmong]

Hi,

Sheet1 is being used as the interface tool for something I'm building. It will contain entry fields a user may enter. I need a VBA macro that will copy data from Sheet1 (specific cell) and paste it into Sheet2 (specific cell). It must also be able to clear the data from Sheet1 and allow the user to re-enter new data. When the user re-enters new data, it will copy into Sheet2, but in the cell below the previous saved data. Please let me know if this is feasible in Excel. Hiearchy will only let analysts play with Access, but it will take years before they would even get on this project and I need to use it asap. Any help is great. Thanks!

Example:
- Data is entered into Sheet1 A1
- Command Button to run Macro
- Data in Sheet1 A1 is copied and pasted to Sheet2 A2
- Data in Sheet1 A1 is cleared after paste
- User can re-enter new data into Sheet1 A1, data is copied and pasted into Sheet2 A3.

 

[Michael M]

is data to be copied across in sheet 1 the entire row, a cell, a range of cells, a column, etc
I note you say Sheet 1 A1 to Sheet 2 A2, but it could be more than 1 cell ??
and where in Sheet 2 will it be pasted.....you say specific, but you need to impart that info to us as well.
Yes it is feasible, and quite readily done in Excel

 

[lrobbo314]

Michael asked a lot of good questions that you should consider before getting too far along with this, but in the meantime, i think this will do what you are asking for.

Private Sub CommandButton1_Click()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim r1 As Range, r2 As Range
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set r1 = ws1.Range("A1")
Set r2 = ws2.Range("A2")
If r2.Value = "" Then
    r2.Value = r1.Value
ElseIf r2.Offset(, 1) = "" Then
    r2.Offset(, 1) = r1.Value
Else
    r2.End(xlToRight).Offset(, 1).Value = r1.Value
End If
r1.ClearContents
End Sub

 

[excel length]

deleted.

 

[excel length]

you can try this code below.
this will cut and paste the value in A1 in sheet1 to the last blank cell in column A in sheet2. (same as with your example)

Sub Cutinsert()
'
' Cut insert
'
'
Range("A1").Select
Selection.Cut
Sheets("Sheet2").Select

If Range("A1") <> "" Then
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Else
Range("A65536").End(xlUp).Select
ActiveSheet.Paste
End If
Sheets("Sheet1").Select
Range("A1").Select

End Sub

sorry about my previous post, i paste the wrong code..

 

[Michael M]

If you are simply moving 1 cell then use:

Sub MM1()
Dim lr As Long
lr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Sheet1").Range("A1").Cut Destination:=Sheets("Sheet2").Range("A" & lr + 1)
End Sub

 

[courageoushmong]

Thank you all for the replies.

Clarifcation:
Sheet1 is treated as an interface where a user enters required data. A macro or set of macros are needed to run (via command button or manual macro run) to copy and paste the entered data into a continuous list in Sheet2.

Scenario:
Sheet1 Cell A1 is where a user would enter a person's name. When macro is ran, text in Sheet1 Cell A1 is copied and pasted (or transferred) to Sheet2 Column A in next available/blank cell. After completion of macro, text Sheet1 Cell A1 is cleared to allow the user to enter the next person's name.

So I type in "John Doe" into Sheet1 Cell A1 and run the macro. "John Doe" is copied and pasted into Sheet2 in the first available/blank cell (Sheet2 Cell A1). Sheet1 Cell A1 is cleared and so I type in "Jane Doe". "Jane Doe is copied and pasted into Sheet2 in the next available/blank cell (in this case, Sheet2 Cell A2).

I hope this helps?

 

[Michael M]

So my code should do the trick then....