I need help with data entry

[Guvnor]

Say if you have got a form where you enter data for the Following deatils: Name , Age and address, how can you transfer this data to another sheet using a macro?What coding is needed?

 

[LSTAN]

Hi

This is one way to do it,

Private Sub CommandButton1_Click()

Dim myRowCount As Integer
Dim myRng As Range

Set myRng = Sheets("sheet2&quot.Range("a:a&quot
myRowCount = Application.CountA(myRng)

With Sheets("sheet2&quot
.Cells(myRowCount + 1, 1).Value = Me.Range("a1&quot.Value
.Cells(myRowCount + 1, 2).Value = Me.Range("b1&quot.Value
.Cells(myRowCount + 1, 3).Value = Me.Range("c1&quot.Value
End With

End Sub

Hope this helps.

Regards
LSTAN

 

[Guvnor]

if i want to transfer 3 values from a sheet called ' insert balance sheet' to another sheet called 'company balance sheet, what needs to be changed in this coding as it says the Me function is invalid.

Dim myRowCount As Integer
Dim myRng As Range

Set myRng = Sheets("insert balance data&quot.Range("2:1&quot
myRowCount = Application.CountA(myRng)

With Sheets("company balance sheet&quot
.Cells(myRowCount + 10, 10).Value = .Range("a1&quot.Value
.Cells(myRowCount + 11, 11).Value = .Range("b1&quot.Value
.Cells(myRowCount + 12, 12).Value = .Range("c1&quot.Value
End With

End Sub


Reagards Guvnor

 

[acron]

Are you using some additional code, as the ME keyword does not seem to appear the extract you posted.

I wonder if

.Cells(myRowCount + 10, 10).Value = .Range("a1&quot.Value

should read

.Cells(myRowCount + 10, 10).Value = MyRng.Range("a1&quot.Value

???

A.C.

 

[Guvnor]

yes acron thats great that works qutie well. two other questions though. im using the following code:

Dim myRowCount As Integer
Dim myRng As Range

Set myRng = Sheets("insert balance data&quot.Range("1:1&quot
myRowCount = Application.CountA(myRng)

With Sheets("company balance sheet&quot
.Cells(myRowCount + 5, 3).Value = myRng.Range("b8&quot.Value
.Cells(myRowCount + 6, 3).Value = myRng.Range("b9&quot.Value
.Cells(myRowCount + 8, 3).Value = myRng.Range("b11&quot.Value
End With

End Sub

But for example instaed of the value being transeferred form cell b8 in the "insert balance sheet" to row 5 column 3 in the "company balance sheet" it is being transeferred to row6 column 3 any reason why?

Also what coding is needed to remove the value that you are transferring to another sheet form the original sheet?

 

[acron]

You probably need to use :

myRowCount = Application.CountA(myRng) - 1

A.C.

 

[Guvnor]

Sweet that did the trick one final question though:

what added code is needed to remove the value that you are transferring to another sheet, from the original sheet?

Thanks for your help

 

[Guvnor]

Any ideas on the last thought of the thread?

 

[acron]

could you use

MyRng.ClearContents

when finished copying ?

A.C.