Solved: Excel workbooks as a Database

[Simon_Price]

Good Afternoon,

Before I begin I am not working in my prefered method to create a database....

I have been asked by my company to create a database that acts similar to MS Access, but in MS Excel 2007.

What I am looking for is the method on how to create the Record Set(s)

for example,

Data entry sheet

Cell A1 First Name
Cell B1 Second Name
Cell D1 Role
Cell E1 Department

Cell A3 Course Date
Cell B3 Course taken

I then want the user to click a 'Save' Button which will then transfer this information from from teh Data Entry Sheet into the Record Set where Row 1 is the headers in A, B, C, D and then from Row two downwards is contains the information in a very large table.

Can anyone help me please.

Your time and efforts are all appreciated.

Kind regards

Simon

[mdmackillop]

Hi Simon
Welcome to VBAX
Where does the data from A3, B3 go?

[Simon_Price]

Hi,

Initially it will go into a record set in a seperate sheet called record set.

So say for example

Cell A1 First Name - Simon
Cell B1 Second Name - Price
Cell D1 Role - Business Support
Cell E1 Department - All

Cell A3 Course Date 17/09/2009
Cell B3 Course taken effective managment

The data would go from this sheet and fill a hidden sheet called record set

Headers
A1 = First Name| B1 = Second Name |C1 = Role| D1 = Course Date | E1= Course Taken
Date
A2 = Simon | B2 = Price | C2 = Business Support .....

And then for each entry on the data entry form it will then fill the next row.

So what I need is to take the information from various cells on one sheet, and then place them in a row in the records set sheet.
Each time a row is completed I need excel to start filling the next row.

Does this make sense?

I am looking at building my template tomorrow as I am just designing it on paper first so that I dont encounter too many issues during the build.

Thank you

[mdmackillop]

Option Explicit
Sub CopyData()
Dim i As Long
Dim cel As Range
Dim Source As Range
Dim Tgt As Range
Set Source = Sheets(1).Range("A1:B1,D1:E1,A3:B3")
Set Tgt = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1)
For Each cel In Source
Tgt.Offset(, i) = cel
i = i + 1
Next

End Sub

[Simon_Price]

Awesome thank you for your help!

[mdmackillop]

Hi Simon,
If a thread is Solved, please mark it so using the Thread Tools dropdown.
Regards
MD