software

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??--
girth69
------------------------------------------------------------------------
girth69's Profile: www.excelforum.com/member.php...oamp;userid=23634
View this thread: www.excelforum.com/showthread...hreadid=517329Hi girth69

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

--
Regards Ron de Bruin
www.rondebruin.nlquot;girth69quot; gt; wrote in message
...
gt;
gt; Im trying to create a database on my excel spreadsheet so i can then
gt; analyse this data using graphs etc.....
gt; Basically i have a form with 6 fields on it. What i want to do is to
gt; have people type in entries to this form and then click on a button to
gt; transfer all this data accross into another worksheet under the same
gt; field headings. Now i have the form all set up and ready and a button
gt; attached ready for a macro to be assigned to it. The problem i have is
gt; when i create a macro to transfer the entries across it works the first
gt; time but then every time i do it, it just overwrites the previous entry.
gt; I need the macro to work so that it enters the new data on another line
gt; in the database worksheet and then i can sort out so that it selects
gt; the cell on the form again ready for another entry.
gt;
gt; Can anybody help me?? If need be i can emial the spreadsheet to you to
gt; sort out and then you can make the macro work if its easier??
gt;
gt;
gt; --
gt; girth69
gt; ------------------------------------------------------------------------
gt; girth69's Profile: www.excelforum.com/member.php...oamp;userid=23634
gt; View this thread: www.excelforum.com/showthread...hreadid=517329
gt;

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(quot;Databasequot;).Select
Range(quot;A1quot;).Select
Selection.End(xlDown).Select
Sheets(quot;Entry Formquot;).Select
Range(quot;C6,C8,C10,C12,C14,C16,C18quot;).Select
Range(quot;C18quot;).Activate
Sheets(quot;Databasequot;).Select
Range(quot;A4quot;).Select
Sheets(quot;Entry Formquot;).Select
Selection.Copy
Sheets(quot;Databasequot;).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Sheets(quot;Entry Formquot;).Select
Application.CutCopyMode = False
Selection.ClearContents
Range(quot;C6quot;).Select
End Sub--
girth69
------------------------------------------------------------------------
girth69's Profile: www.excelforum.com/member.php...oamp;userid=23634
View this thread: www.excelforum.com/showthread...hreadid=517329Hi girth69Use this example

In a row on sheets quot;Entry Formquot; 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(quot;Databasequot;)) 1
Set sourceRange = Sheets(quot;Entry Formquot;).Range(quot;A50:G50quot;)
With sourceRange
Set destrange = Sheets(quot;Databasequot;).Range(quot;Aquot; amp; Lr). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
End SubFunction LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:=quot;*quot;, _
After:=sh.Range(quot;A1quot;), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function--
Regards Ron de Bruin
www.rondebruin.nlquot;girth69quot; gt; wrote in message
...
gt;
gt; Sorry but im confused on how this link is supposed to help me??
gt;
gt; It doesnt specifically say which one i need?? How do i input the new
gt; code? Do i just copy and paste it in place of the old macro??
gt;
gt; I only have 6 feilds i need to copy over. I'll copy accross the code i
gt; have in place now
gt;
gt; Sub Macro9()
gt; '
gt; ' Macro9 Macro
gt; ' Macro recorded 28/02/2006 by b2683
gt; '
gt;
gt; '
gt; Sheets(quot;Databasequot;).Select
gt; Range(quot;A1quot;).Select
gt; Selection.End(xlDown).Select
gt; Sheets(quot;Entry Formquot;).Select
gt; Range(quot;C6,C8,C10,C12,C14,C16,C18quot;).Select
gt; Range(quot;C18quot;).Activate
gt; Sheets(quot;Databasequot;).Select
gt; Range(quot;A4quot;).Select
gt; Sheets(quot;Entry Formquot;).Select
gt; Selection.Copy
gt; Sheets(quot;Databasequot;).Select
gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; SkipBlanks _
gt; :=False, Transpose:=True
gt; Sheets(quot;Entry Formquot;).Select
gt; Application.CutCopyMode = False
gt; Selection.ClearContents
gt; Range(quot;C6quot;).Select
gt; End Sub
gt;
gt;
gt; --
gt; girth69
gt; ------------------------------------------------------------------------
gt; girth69's Profile: www.excelforum.com/member.php...oamp;userid=23634
gt; View this thread: www.excelforum.com/showthread...hreadid=517329
gt;

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

Sheets(quot;Databasequot;).Select
Range(quot;A1quot;).Select
Selection.End(xlDown).Select
Sheets(quot;Entry Formquot;).Select
Range(quot;D6,D8,D10,D12,D14,D16,D18quot;).Select
Range(quot;D18quot;).Activate
Selection.Copy
Sheets(quot;Databasequot;).Select
ActiveCell.Offset(1, 0).Range(quot;A1quot;).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Sheets(quot;Entry Formquot;).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.ClearContents
ActiveCell.Offset(-12, 0).Range(quot;A1quot;).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!!--
girth69
------------------------------------------------------------------------
girth69's Profile: www.excelforum.com/member.php...oamp;userid=23634
View this thread: www.excelforum.com/showthread...hreadid=517329