Transfer data from Sheet1 to Sheet2

[dreid1011]

Ok, so I am trying to make a form on sheet1 for user input with a button to record the data on sheet2. For the life of me, I cannot figure out why I cannot get the data to sheet2. It worked one time after I used the record macro and I duplicated what was in the module. But now, I cannot seem to get anything to work.

I was hoping to avoid jumping back and forth between the sheets and copy/pasting each line, but I was having trouble using variables as well.

Here is what I have:

Sub Record_Click()
    Range("B5").Select
    Selection.Copy
    Sheets("Vet List").Activate
    ActiveSheet.Range("B1").End(xlDown).Select
    ActiveSheet.Selection.Offset(1, 0).Paste
End Sub

 

[iggydarsa]

Sub Record_Click()
    lr = Worksheets("Vet List").Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("B5").Copy Worksheets("Vet List").Cells(lr, "B")
End Sub

 

[dreid1011]

Ok. I apologize now if I don't understand what you mean right away. But you will have to give me more than just a block of code with no explanation.

Right now, I just want to work on getting that one cell's value transferred to sheet2. Once I get that, I can figure out the rest.

This is the form I am using, the cells are in no easily defined order as far as input:
Imgur

 

[iggydarsa]

lr = Worksheets("Vet List").Range("B" & Rows.Count).End(xlUp).Row + 1

lr is the "last empty row" on Column B of "Vet List" sheet. For example if the last entry on Column B of Vet List was row # 8, then lr will be 9.
----------------------------------------------------------------------------------

Range("B5").Copy Worksheets("Vet List").Cells(lr, "B")

the value on B5 of your main sheet where the form is, is copied to where Column is B and the Row is lr. If continue with the same example, lr was 9 so it will paste the value of B5 into B9 on the Vet List sheet.

lets say, you will want to transfer Map # on the Column C as well, then you would change your code to

Sub Record_Click()
    lr = Worksheets("Vet List").Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("B5").Copy Worksheets("Vet List").Cells(lr, "B")
    Range("D5").Copy Worksheets("Vet List").Cells(lr, "C")
End Sub

Makes sense?

 

[dreid1011]

Yes, it helps a bunch, thank you. Now, just a question for curiosity sake. Why is End(xlUp) looking up instead of down?

 

[iggydarsa]

Yes, it helps a bunch, thank you. Now, just a question for curiosity sake. Why is End(xlUp) looking up instead of down?

It is a preference, but I like this way better because if there is a gap then it wouldn't go all the way to the end. Imagine, there is data on B1, B2, B3 and B5.... B4 is empty for some weird reason. xlDown would jump to B3, but the way I do it, it jumps to B5 because it is jumping all the way from the bottom.

As I said it is a matter of preference but I find the xlUp method safer.

 

[dreid1011]

It is a preference, but I like this way better because if there is a gap then it wouldn't go all the way to the end. Imagine, there is data on B1, B2, B3 and B5.... B4 is empty for some weird reason. xlDown would jump to B3, but the way I do it, it jumps to B5 because it is jumping all the way from the bottom.

As I said it is a matter of preference but I find the xlUp method safer.

Ah ok, I was misunderstanding where it was starting from then. It makes sense. Thank you.