Transfer entered data from Sheet1 to Sheet2

courageoushmong

New Member
Joined
Feb 5, 2013
Messages
3
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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 14, 2008
Messages
2,839
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.

Code:
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

New Member
Joined
Jan 29, 2013
Messages
19

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
If you are simply moving 1 cell then use:
Code:
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

New Member
Joined
Feb 5, 2013
Messages
3
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
So my code should do the trick then....:cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,680
Messages
5,524,249
Members
409,566
Latest member
MickB

This Week's Hot Topics

Top