updating data lists using macros

[taniedzw]

I have a big list of data that must be added to daily. Severa
different columns must be entered and then sorted by date. I want t
create a way to enter the required data on a "cover page" using
custom entry form. So I want to know how to make a macro do th
following:

user enters data into form, then clicks "Update" button with assigne
macro

Macro then:

takes the data from my form
enters it in the data base (which is located on a seperate sheet in th
file)
clears form for next entry

I can make it enter one row of data, but I don't know how to incremen
it to have it enter data on the last available row each time

 

[steveB]

This code will copy row 1 of Sheet1 to the first free row on Sheet2 and than
clear row 1 on Sheet1.
This code presumes that there is always an entry in column A.
Change the sheet names to match your workbook. Adjust the copy row to
reflect your form.

'''''''''''''''''''''' Untested code. Should work.
Sub TransferData()
Dim lrow As Long

' first free row on Sheet2
lrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row

' copy row 1 of Sheet1 to first free row on Sheet2
Sheets("Sheet1").Rows(1).Copy _
Destination:=Sheets("Sheet2").Cells(lrow, 1)

' clear row 1 on Sheet1
Sheets("Sheet1").Rows(1).ClearContents

End Sub
''''''''''''''''''''''''''''
hth

 

[Oreg]

Hello,

I am a total beginner at this and know enough about excel to b
dangerous.. but I couldn't resist responding to your questions as I'v
just recently found what you may be looking for on this board. I'v
created a user form which is made of comboboxes and textboxes tha
enter data into a sheet I've named "METRO" The code pasted belo
searches for the next "empty" cell in METRO and enters the info plugge
into the form. See below:
Private Sub ADD_Click()

Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("METRO")
With ws
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With
r = ws.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row
ws.Range("C" & r).Value = ComboBox1.Value
ws.Range("E" & r).Value = rngsw.Value
ws.Range("F" & r).Value = rngfail.Value
ws.Range("H" & r).Value = ComboBox2.Value
ws.Range("I" & r).Value = ComboBox3.Value
ws.Range("J" & r).Value = ComboBox4.Value
ws.Range("K" & r).Value = ComboBox5.Value
ws.Range("L" & r).Value = ComboBox6.Value
ws.Range("N" & r).Value = TotHH.Value
ws.Range("O" & r).Value = TotMM.Value
ws.Range("T" & r).Value = ComboBox7.Value
ws.Range("AA" & r).Value = ComboBox8.Value
ws.Range("AD" & r).Value = Timestart.Value
ws.Range("AE" & r).Value = Timeend.Value
ws.Range("M" & r).Value = ComboBox9.Value
ComboBox1.Value = ""
rngsw.Value = ""
rngfail.Value = ""
ComboBox2.Value = ""
ComboBox3.Value = ""
ComboBox4.Value = ""
ComboBox5.Value = ""
ComboBox6.Value = ""
TotHH.Value = ""
TotMM.Value = ""
ComboBox7.Value = ""
ComboBox8.Value = ""
Timestart.Value = ""
Timeend.Value = ""
ComboBox9.Value = ""
ComboBox2.SetFocus
End Sub

Hope this helps!