Excel Database

[girth69]

Im trying to create a database on my excel spreadsheet so i can then
analyse this data using graphs etc.....
Basically i have a form with 6 fields on it. What i want to do is to
have people type in entries to this form and then click on a button to
transfer all this data accross into another worksheet under the same
field headings. Now i have the form all set up and ready and a button
attached ready for a macro to be assigned to it. The problem i have is
when i create a macro to transfer the entries across it works the first
time but then every time i do it, it just overwrites the previous entry.
I need the macro to work so that it enters the new data on another line
in the database worksheet and then i can sort out so that it selects
the cell on the form again ready for another entry.

Can anybody help me?? If need be i can emial the spreadsheet to you to
sort out and then you can make the macro work if its easier??

 

[Ron de Bruin]

Hi girth69

See this page
http://www.rondebruin.nl/copy1.htm

 

[girth69]

Sorry but im confused on how this link is supposed to help me??

It doesnt specifically say which one i need?? How do i input the new
code? Do i just copy and paste it in place of the old macro??

I only have 6 feilds i need to copy over. I'll copy accross the code i
have in place now

Sub Macro9()
'
' Macro9 Macro
' Macro recorded 28/02/2006 by b2683
'

'
Sheets("Database").Select
Range("A1").Select
Selection.End(xlDown).Select
Sheets("Entry Form").Select
Range("C6,C8,C10,C12,C14,C16,C18").Select
Range("C18").Activate
Sheets("Database").Select
Range("A4").Select
Sheets("Entry Form").Select
Selection.Copy
Sheets("Database").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Sheets("Entry Form").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C6").Select
End Sub

 

[Ron de Bruin]

Hi girth69


Use this example

In a row on sheets "Entry Form" below you data add formulas like this(8*)

in A50 =C6
in B50 =C8
.......

You can hide this row

Sub copy_1_Values_ValueProperty()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Lr = LastRow(Sheets("Database")) + 1
Set sourceRange = Sheets("Entry Form").Range("A50:G50")
With sourceRange
Set destrange = Sheets("Database").Range("A" & Lr). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

 

[girth69]

Right, lets start afresh here cos it isnt working....heres my code
currently

Sheets("Database").Select
Range("A1").Select
Selection.End(xlDown).Select
Sheets("Entry Form").Select
Range("D6,D8,D10,D12,D14,D16,D18").Select
Range("D18").Activate
Selection.Copy
Sheets("Database").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Sheets("Entry Form").Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.ClearContents
ActiveCell.Offset(-12, 0).Range("A1").Select
End Sub

basically. every time i click on the button on the 'form' worksheet. it
comes up with an error and on the 'database' worksheet it seems it keeps
going to the bottom cell on the entire worksheet. can you enter the code
into my above code and let me know what i have to do on my worksheets to
change this?? sorry but you might have to treat my like an idiot here!!