Help with a macro

[NEHicks]

I have a form that people enter daily information in. I need to be able to
retain the data each day. So, my thought was to link cells to a second
worksheet. How do I make the 2nd worksheet switch to the next row after the
last column is filled in so that the next day has a new row to take data in?

 

[Jim Thomlinson]

So you want a data input form on one sheet and a database on the other. that
sounds like a good way to proceed. Taht being said you are not so much
looking to link the data form to the database but rather allow the end user
to append data to the database base on their form inputs.

My recommendation would be to add a button to the input form that copies the
relevant values to the next available row on the database sheet. The code
could be similar to this...

Private Sub CommandButton1_Click()
Dim rng As Range

Set rng = Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
With rng
.Value = IIf(Application.IsText(.Offset(-1, 0).Value), 0, _
.Offset(-1, 0).Value) + 1 'index number
.Offset(0, 1).Value = Sheet1.Range("A1").Value
.Offset(0, 2).Value = Sheet1.Range("A2").Value
End With
End Sub

 

[Gord Dibben]

Very little detail on form layout and number of cells and addresses involved
so hard to tailor something.

Example code..................

Here is sheet event code using one cell only.........B5

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$5" Then
Application.EnableEvents = False
j = (Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1)
Sheets("Sheet2").Cells(j, 1) = Target.Value
Application.EnableEvents = True
End If
End Sub

Right-click on Sheet1 tab and "View Code". Copy/paste the above into that
module.

Type something in B5 and it will be copied to Sheet2 in Column A

Type again in B5 and will be copied below previous data in Sheet2


Gord Dibben MS Excel MVP