Transfering data from sheet1 to sheet2

[saxjammin]

Hi
I am quite new to excel formula's and i have been trying to find a resolution to this for some time, please help someone.
I am using a command button on page one and after filling all relative fields click the command button and all my data transfers to page 2 without any mistakes. I have titles above each column from A1 to Y1 and once i have clicked the command button my data is along the path A2 to Y2 with my cursor selected at A3 ready for the next row/data input.
This is where I have a problem, when i try to click the command button again the the data will not transfer onto A3 to Y3 instead the cursor selects cell A1
Can anyone please show me how I can keep inputting data on the row below each time i hit the command button. Please see my code below.

Private Sub Calendar1_Click()
ActiveCell.Value = Calendar1.Value
End Sub

Private Sub CommandButton1_Click()

Worksheets("sheet1").Select
Job = Range("A9")
Date = Range("A11")
Forename = Range("A13")
Surname = Range("B13")
Tel = Range("A15")
Mob = Range("B15")
moving = Range("A17")
Timeline = Range("A19")
Address = Range("A21")
Town = Range("A23")
County = Range("A25")
Postcode = Range("A27")
Country = Range("A29")
Details = Range("A31")
Forename1 = Range("A34")
Surname1 = Range("B34")
Tel1 = Range("A36")
Mob1 = Range("B36")
AddressTo = Range("A38")
Town = Range("A40")
County = Range("A42")
Postcode = Range("A44")
Country = Range("A46")
Details1 = Range("A48")
Worksheets("sheet2").Select
Worksheets("sheet2").Range("A1").Select
If Worksheets("sheet2").Range("A1").Offset(1, 0) <> "" Then
Worksheets("sheet2").Range("A1").End(x1down).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Job
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Date
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Forename
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Surname
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Tel
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Mob
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = moving
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Timeline
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Address
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Town
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = County
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Postcode
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Country
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Details
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = Forename
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Surname
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Tel1
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Mob1
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = AddressTo
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Town
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = County
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Postcode
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Country
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Details1
ActiveCell.Offset(1, -24).Select

Worksheets("sheet1").Select
Worksheets("sheet1").Range("A9").Select









End Sub

 

[Joe4]

Re: Please help transfering data from sheet1 to sheet2

You don't need all those selects in there (they slow down the code), and you don't actually need to store every value as a variable.
Try something like this:

Private Sub CommandButton1_Click()

    Dim LR As Long
    
'   Find last row in column A with data on sheet 2
    Worksheets("Sheet2").Select
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Populate values
    Cells(LR + 1, "A") = Sheets("Sheet1").Range("A9")   'Job
    Cells(LR + 1, "B") = Sheets("Sheet1").Range("A11")  'Date
    Cells(LR + 1, "C") = Sheets("Sheet1").Range("A13")  'Forename
    Cells(LR + 1, "D") = Sheets("Sheet1").Range("B13")  'Surname
    Cells(LR + 1, "E") = Sheets("Sheet1").Range("A15")  'Tel
    Cells(LR + 1, "F") = Sheets("Sheet1").Range("B15")  'Mob
    Cells(LR + 1, "G") = Sheets("Sheet1").Range("A17")  'Moving
    Cells(LR + 1, "H") = Sheets("Sheet1").Range("A19")  'Timeline
    Cells(LR + 1, "I") = Sheets("Sheet1").Range("A21")  'Address
    Cells(LR + 1, "J") = Sheets("Sheet1").Range("A23")  'Town
    Cells(LR + 1, "K") = Sheets("Sheet1").Range("A25")  'County
    Cells(LR + 1, "L") = Sheets("Sheet1").Range("A27")  'Postcode
    Cells(LR + 1, "M") = Sheets("Sheet1").Range("A29")  'Country
    Cells(LR + 1, "N") = Sheets("Sheet1").Range("A31")  'Details
    Cells(LR + 1, "P") = Sheets("Sheet1").Range("A34")  'Forename1
    Cells(LR + 1, "Q") = Sheets("Sheet1").Range("B34")  'Surname1
    Cells(LR + 1, "R") = Sheets("Sheet1").Range("A36")  'Tel1
    Cells(LR + 1, "S") = Sheets("Sheet1").Range("A38")  'AddressTo
    Cells(LR + 1, "T") = Sheets("Sheet1").Range("A40")  'Town
    Cells(LR + 1, "U") = Sheets("Sheet1").Range("A42")  'County
    Cells(LR + 1, "V") = Sheets("Sheet1").Range("A44")  'Postcode
    Cells(LR + 1, "W") = Sheets("Sheet1").Range("A46")  'Country
    Cells(LR + 1, "X") = Sheets("Sheet1").Range("A48")  'Details1

End Sub

Note: Hopefully, I got all the column references correct, but you may want to double-check that to make sure I didn't mess any up trying to translate over from your code.

 

[saxjammin]

Re: Please help transfering data from sheet1 to sheet2

Hi
Thank you so much for providing the code yesterday, I was really looking forward to try it this morning but unfortunately I cannot get it to work. I copied the code you sent me but when I click command button it puts all the information on the next available line on sheet1 below my input fields, nothing is transferred to sheet2 I really need this information on sheet2 I have tried a few things but I cannot get it to work, any ideas please, I would be very grateful.
Kind Regards
Philip

 

[Joe4]

Re: Please help transfering data from sheet1 to sheet2

Seer if it makes a difference to qualify each line by specifying the sheet number in front of it, i.e.

[FONT=Verdana]Sheets("Sheet2").[/FONT]Cells(LR + 1, "A") = Sheets("Sheet1").Range("A9")   'Job

You can try it with one or two lines first, and if it works, add it to all of them.

 

[saxjammin]

Re: Please help transfering data from sheet1 to sheet2

Thanks for the response, I thought it looked good but sorry to no avail, it still does not work?

 

[Joe4]

Re: Please help transfering data from sheet1 to sheet2

Are you sure your Command Button is running the correct code?
What module have you placed this code in (in a Sheet Module or a Standard Module)? It should be in a Standard Module.
What is you go into the VB Edtior, highlight the first line of code, and run it from there?

 

[My Aswer Is This]

Re: Please help transfering data from sheet1 to sheet2

This script as provided works for me.