How to enter data from a form on one sheet onto a log on another sheet

Question

So I'm trying to figure out how I can setup a macro that will take the data that I enter into a form on one sheet then log it into a log in another sheet. It will log it but my big problem is that it needs to go to the next line and I can't quite figure out the code for it. Here is what my code looks like:

Sub Appt()


'
' Appt Macro
'
'

Range("E4").Select
Selection.Copy
Sheets("Appointments").Select
Range("G7").Select
ActiveSheet.Paste
Sheets("Data Entry").Select
Range("E6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Appointments").Select
Range("D7").Select
ActiveSheet.Paste
Sheets("Data Entry").Select
Range("E8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Appointments").Select
Range("E7").Select
ActiveSheet.Paste
Sheets("Data Entry").Select
Range("E10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Appointments").Select
Range("F7").Select
ActiveSheet.Paste
Sheets("Data Entry").Select
Range("E12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Appointments").Select
Range("H7").Select
ActiveSheet.Paste
Sheets("Data Entry").Select
Range("E4").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E6").Select
Selection.ClearContents
Range("E8").Select
Selection.ClearContents
Range("E10").Select
Selection.ClearContents
Range("E12").Select
Selection.ClearContents
End Sub

score 2 · Answer 1 · edited May 23 '17 at 10:26

To get the next empty row on Sheets("Appointments") you would use this formula to get the row number:

 tRw = Sheets("Appointments").Range("D" & Rows.count).End(xlUp).Offset(1).Row

This assumes that there is nothing in column D below what you are pasting.

It is apparent that you used the macro recorder, and this is a great way to learn. But using the .select so much will slow things down and is unneeded.

To get around that declare the sheets as variables and then one line for each copy paste is needed.

Sub APPT()
Dim oWs As Worksheet
Dim tWs As Worksheet
Dim tRw As Long

Set oWs = Sheets("Data Entry")
Set tWs = Sheets("Appointments")
tRw = tWs.Range("D" & Rows.count).End(xlUp).Offset(1).Row

With oWs
    .Range("E4").copy tWs.Range("G" & tRw)
    .Range("E6").copy tWs.Range("D" & tRw)
    .Range("E8").copy tWs.Range("E" & tRw)
    .Range("E10").copy tWs.Range("F" & tRw)
    .Range("E12").copy tWs.Range("H" & tRw)
    .Range("E4").ClearContents
    .Range("E6").ClearContents
    .Range("E8").ClearContents
    .Range("E10").ClearContents
    .Range("E12").ClearContents
End With
End Sub

For other methods of finding the next row look at Siddharth Rout's answer here.

And as BruceWayne stated in his comment, this is a great reference as to why/how to avoid using .select

To add to the idea that you shouldn't use `.select`, here's a [SO question](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) that has some good information on how/why to avoid it. — BruceWayne, Oct 05 '15 at 18:54
@BruceWayne Thanks, I was just about to go look for that reference. — Scott Craner, Oct 05 '15 at 18:55

score 1 · Answer 2 · answered Oct 05 '15 at 19:29

It's best to avoid the user's clipboard and to assign the values directly:

Sub Appt()
    Dim n&, v
    v = [transpose(offset('data entry'!e4,{0;2;4;6;8},))]
    With Sheets("appointments")
        n = .Range("d" & .Rows.Count).End(xlUp).Row
        .[g1].Offset(n) = v(1)
        .[d1].Offset(n) = v(2)
        .[e1].Offset(n) = v(3)
        .[f1].Offset(n) = v(4)
        .[h1].Offset(n) = v(5)
    End With
    Sheets("data entry").Range("e4,e6,e8,e10,e12").ClearContents
End Sub

How to enter data from a form on one sheet onto a log on another sheet

2 Answers2