VBA help, moving data.

[mgcchkn]

Hey there, new guy here. I apologize in advance.

Looking to create the following:

On submit, take user input from sheet "entry form" cells b6, d6, g6 and b9 and insert it into a new row at the top of sheet "database" starting with current date, current time, username, then the data from "entry form."

On successful submit, auto save spreadsheet and reset the cells on "entry form" back to blank (or default setting). And popup saying something witty to confirm success.

It sounds super easy in my head, I even have another example to reverse engineer, but I can't get it to work. This is killing me inside!

Thanks

 

[alansidman]

You may want to attach this to a command button for ease.

Option Explicit


Sub transfer()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("entry form"): Set s2 = Sheets("database")
    Dim lr As Long: lr = s2.Range("A" & Rows.Count).End(xlUp).Row
    s2.Range("A" & lr + 1) = Date
    s2.Range("B" & lr + 1) = Time
    s2.Range("C" & lr + 1) = Environ("username")
    s2.Range("D" & lr + 1) = s1.Range("B6")
    s2.Range("E" & lr + 1) = s1.Range("D6")
    s2.Range("F" & lr + 1) = s1.Range("G6")
    s2.Range("G" & lr + 1) = s1.Range("B9")
    Application.Union(s1.Range("B6"), s1.Range("D6"), s1.Range("G6"), s1.Range("B9")).ClearContents
    ActiveWorkbook.Save
    MsgBox ("Witty Saying that work has been saved")
End Sub

 

[mgcchkn]

It's working. But it's giving me an error that just says "400." I stepped through it and at the clear contents it said it can't do this to a merged cell.

I do have merged cells at D6, G6 and B9.

Also, is there a way to make it insert a new row at the top instead of adding to the bottom?

I appreciate the help!

 

[mgcchkn]

If it matters, the merged cells are as follows.

D6 (D6:E6)
G6 (G6:H6)
B9 (B9:H11) (at least I think that's how you say B9:H9+B10:H10+B11:H11)

 

[mgcchkn]

You may want to attach this to a command button for ease.

Sorry for the 3 replies, but I don't see how to edit posts in this forum.

Previously when I'd try to use a command button, the button would shrink and the font on the label would get bigger every time I'd click it.

Had me thoroughly confused so I abandoned the command button.

 

[alansidman]

Excel VBA does not like merged cells. You will need to unmerge the cells for VBA to work.

Also, is there a way to make it insert a new row at the top instead of adding to the bottom?

What line in the current worksheet do you wish to have the data entered into. It is just a matter of inserting a line of code to insert a new line and have the data posted there. But if you wish me to do that for you, I will need to know which row.

 

[mgcchkn]

Excel VBA does not like merged cells. You will need to unmerge the cells for VBA to work.

What line in the current worksheet do you wish to have the data entered into. It is just a matter of inserting a line of code to insert a new line and have the data posted there. But if you wish me to do that for you, I will need to know which row.

Would it work with text boxes instead of merged cells?

Insert row 2.

 

[alansidman]

Option Explicit


Sub transfer()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("entry form"): Set s2 = Sheets("database")
    'Dim lr As Long: lr = s2.Range("A" & Rows.Count).End(xlUp).Row
    s2.Range("A2").EntireRow.Insert
    s2.Range("A2") = Date
    s2.Range("B2") = Time
    s2.Range("C2") = Environ("username")
    s2.Range("D2") = s1.Range("B6")
    s2.Range("E2") = s1.Range("D6")
    s2.Range("F2") = s1.Range("G6")
    s2.Range("G2") = s1.Range("B9")
    Application.Union(s1.Range("B6"), s1.Range("D6"), s1.Range("G6"), s1.Range("B9")).ClearContents
    ActiveWorkbook.Save
    MsgBox ("Witty Saying that work has been saved")
End Sub

Create a Form with Text boxes and the same principle should work. Need to change range sources to textboxes, however.

ie.

s2.range("A2")= me.textbox1

etc.

 

[mgcchkn]

Last thing (I think), how do I get the text boxes to clear? I tried replacing the s1.Range("D6") with Me.Textbox1 and it didn't work. Runtime error 13 Type Mismatch.

(also, do you know anything about the command button issue I spoke of?)

 

[alansidman]

Me.textbox1=""
Me.textbox2=""
etc.
Me.requery

I have no idea what the issue is with your command button. Nothing I have ever heard of or seen.