Simple data entry form above the targeted columns. Help!

[mrmikepan]

[h=2]

[/h]

So... I have a table with 4 columns, these are the headers:
Date / Item Name / Purchase From / Purchase Price
Table Starts @ B3, Headers C3 / D3 / E3 / F3 (Same order as above)
I want to be able to type in the data in C4 / D4 / E3 / F3, press an 'Enter' Button Near G4.
It will automatically place the data in the next open row for each column. "Adding it to the List"

Seems simple, but I have no clue... If someone could show me what to do I would be so appreciative and hopefully I'll be able to modify the efficiency of how I want to enter the data from there.

Thank You!

PS: Still learning so explanations of the code are soo welcome

Alternative explanation of what I'm trying to do:

I have 4 cells that I want to enter data into C4,D4,E4,F4.
Underneath those 4 cells its suppose to be a list of what I've entered.
I want to type data into C4,D4,E4,F4... Push a developer button labeled "Enter" and then have that data be put onto the bottom of the list, clearing C4,D4,E4, and F4 for the next set of data to be added to the list.

To do this it must go down the list and find the first blank row for the data from (C4,D4,E4,F4) to be placed, When the developer button labeled "Enter" is pressed. At least I think that's the easiest way to accomplish this? I don't know VB code.​

 

[L. Howard]

Either of these should do it.
With Sub CopyFourByA() select the four cell C4, D4, E4, F4 and name them FourBy (or a name to suit you modify the first code to that name)
If going past 1000 rows down change C1000 to Cnn,nnn

Option Explicit
Sub CopyFourByA()
Range("FourBy").Copy Range("C1000").End(xlUp).Offset(1, 0)
Range("FourBy").ClearContents
Range("C4").Select
End Sub

Sub CopyFourByB()
Range("C4:F4").Copy Range("C1000").End(xlUp).Offset(1, 0)
Range("C4:F4").ClearContents
Range("C4").Select
End Sub

Regards,
Howard

 

[mrmikepan]

Either of these should do it.
With Sub CopyFourByA() select the four cell C4, D4, E4, F4 and name them FourBy (or a name to suit you modify the first code to that name)
If going past 1000 rows down change C1000 to Cnn,nnn

Option Explicit
Sub CopyFourByA()
Range("FourBy").Copy Range("C1000").End(xlUp).Offset(1, 0)
Range("FourBy").ClearContents
Range("C4").Select
End Sub

Sub CopyFourByB()
Range("C4:F4").Copy Range("C1000").End(xlUp).Offset(1, 0)
Range("C4:F4").ClearContents
Range("C4").Select
End Sub

Regards,
Howard

Thanks Howard! Fast Reply and just what I needed to be put in the right direction! I appreciate it!

 

[L. Howard]

You are welcome, thanks for the feedback.

Regards,
Howard

 

[mrmikepan]

ADVERTISEMENT

You are welcome, thanks for the feedback.

Regards,
Howard

Howard! I need help again, how do I make this macro Sheet Specific? I'm getting a Run-time error '1004': Method 'Range' of object'_Global' failed

Thank You

 

[L. Howard]

Not sure what's going on.

I ran both macros from sheet 1 module, ThisworkBook module and from an insert module. A copied without error.

Are you using the one with the named range FourBy? And is the name spelled correctly?

Howard

 

[mrmikepan]

ADVERTISEMENT

Not sure what's going on.

I ran both macros from sheet 1 module, ThisworkBook module and from an insert module. A copied without error.

Are you using the one with the named range FourBy? And is the name spelled correctly?

Howard

I got the error to go away, by renaming the cells to FourBy because the name change when I was enabling macros. Anyways Its copying correctly, but its appearing at the bottom of the table, idk why, its like I have the 4 columns in a table so its starting the list at the end of the table instead of at the top underneath the FourBy cells.

Let me explain exactly what I'm working with:

"Date" is C7, "Item Name" is D7, "Purchased From" is E7, and "Purchase Price" is F7... This is where I want to enter the data, and I want the entry to be added to the list RIGHT underneath @ C8, D8, E8, F8.
As you enter more data sets, the whole list shifts down to allow the newest entry to be in C8, D8, E8, F8.

Thanks again for sticking with me through this.

 

[L. Howard]

Try this.

Option Explicit
Sub CopyFourByA()
If WorksheetFunction.CountA(Range("C7:F7")) = 0 Then
    MsgBox "Nothing to copy!"
    Exit Sub
 Else
   Range("FourBy").Select
    Selection.Copy
    Range("C7").Select
    Selection.Insert Shift:=xlDown
    Range("FourBy").ClearContents
    Range("C7").Select
End If
End Sub

Regards,
Howard

 

[mrmikepan]

Sorry for not getting back to you right away. I was away from my computer, anyways, When I press the button... Run-time error '1004': This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet. I wish I wasn't so clueless about every little detail of this stuff

Additionally, the "Nothing to Copy" worked :P when the field was blank, so if anything that was a nice addition

Try this.

Option Explicit
Sub CopyFourByA()
If WorksheetFunction.CountA(Range("C7:F7")) = 0 Then
    MsgBox "Nothing to copy!"
    Exit Sub
 Else
   Range("FourBy").Select
    Selection.Copy
    Range("C7").Select
    Selection.Insert Shift:=xlDown
    Range("FourBy").ClearContents
    Range("C7").Select
End If
End Sub

Regards,
Howard

 

[L. Howard]

I am at a loss to fix that problem.

Is it necessary that you use a table?

I'll poke around and see what, if anything, I can do.

Howard