Auto Index Inputted Data to Next Available Row

[Xcel Dummie]

Hello Everyone, first post here but I can tell this forum is full of talented people from reading previous posts.

I have a situation at work that I am desperately hoping you experts can help me out with. I have a machine that is performing a test on one of our products. The test results (3 data points) are automatically inputted into excel. The test machine software only allows me the ability to choose a cell where the results are inputted. Every time I run a test the information is stored in cell A1,A2 and A3 and overwrites the previous test entry.

Is there a macro or program that can be written for the excel workbook that says every time a new result is inputted to index it to the next available row? Keep in mind the machine that is inputting the data is telling the data to go to cell A1, A2, and A3.

Maybe a macro that says every time new data points are entered into sheet 1 cells A1-A3 to take them and put them into sheet 2 on the next available row?

Any help would be tremendously appreciated.

[Xcel Dummie]

Bumptie bump bump. Anyone able/willing to help me out with this?

[Chippy]

Try this code in the sheet module of Sheet1. It works for me when A1-A3 are inputted manually, but I'm not sure if it will also work when these cells are inputted automatically.

Please Login or Register  to view this content.

[Xcel Dummie]

Thanks Chippy,
I tried this and get an invalid outside procedure, Complie error at the STatic values line.

[Xcel Dummie]

Ok disregard that. I changed the A1:A3 on the first if not command line to A1:C1 since the data is populated into excel in a row. I am still getting a run-time error object required. Any advice?

[Chippy]

It always helps to describe the problem accurately . To handle input in A1:C1 instead the code is:

Please Login or Register  to view this content.

[Xcel Dummie]

Thank you. Would this be a macro? Please pardon the ignorance, I am trying to learn.

[Xcel Dummie]

So this is what I have. Can someone please tell me what I am doing wrong?

Private Sub CommandButton1_Click()
Run (Worksheet_Change)
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lr As Long
Static values(2) As Variant

If Not Intersect(Range("A1:C1"), Target) Is Nothing Then
values(Target.Column - 1) = Target.Value
If Not IsEmpty(values(0)) And Not IsEmpty(values(1)) And Not IsEmpty(values(2)) Then
With Worksheets("Sheet2")
lr = .Cells(Rows.Count, "A").End(xlUp).Row
If Not IsEmpty(.Cells(lr, "A").Value) Then lr = lr + 1
.Cells(lr, "A").Resize(1, 3).Value = values
End With
Erase values
End If
End If

End Sub

[Xcel Dummie]

My worksheet attached.

Book2.xlsm

[Xcel Dummie]

Bump it up! Please?

[Chippy]

I downloaded your workbook, but got the error "Can't exit design mode because Control 'CommandButton1' cannot be created" when I entered data in A1. You have put my code correctly in the Sheet1 module, but you don't need the command button nor the userform that you added.

Start again by putting my code in the Sheet1 module of a new workbook. You don't need anything else at this stage, as the code runs automatically when you enter data on Sheet1. On Sheet1, enter data in cells A1, B1, C1. After entering the C1 cell, the 3 cells should be copied to the next available row on Sheet2.

PS when posting code please put in within [ CODE] [ /CODE] tags (without the spaces).