Forms and Data

[Guest]

Hi,
I am newbie with Excel so please tolerate my questions.
I am trying to incorporate a form and a database in the one file.
Eg - If I used the pre-defined Invoice template, how can I accumulate the
data in a list for each and every Invoice that is created.

Not sure if I am clear with what I need, but what I envisaged, was the first
page containing the Invoice Template where the details are entered, making
this a printable invoice, but as this information is entered, I need the
information to go into the second tab of the file which ideally would
accumulate all and every detail from the invoices.

Is it at all possible ?
Thank you in advance for your help...

 

[Bernie Deitrick]

Dinky,

You could use a macro. Let's say that you have two sheets: 'Invoice Form' and 'Database'

Your macro could be along these lines (cell addresses and columns would need to be customized, of
course)

Sub Transfer()
Dim myRow As Long
Dim shtF As Worksheet
Dim shtDB As Worksheet

Set shtF = Worksheets("Invoice Form")
Set shtDB = Worksheets("Database")

'Find the first free row in the database
myRow = shtDB.Cells(Rows.Count,1).End(xlUp)(2).Row

'Transfer the data
shtDB.Cells(myRow,1).Value = shtF.Range("D1").Value
shtDB.Cells(myRow,2).Value = shtF.Range("D3").Value
shtDB.Cells(myRow,3).Value = shtF.Range("H12").Value
shtDB.Cells(myRow,4).Value = shtF.Range("C15").Value

'Clear theForm (This part is optional)
shtF.Range("D1").ClearContents
shtF.Range("D3").ClearContents
shtF.Range("H12").ClearContents
shtF.Range("C15").ClearContents

End Sub

I hope you can see the structure and pattern..... The macro could be assigned to a button on the
"Invoice Form" worksheet with the title "Transfer data and clear form"

HTH,
Bernie
MS Excel MVP