Help... VBA Code to copy and paste value in a new sheet in last row

[santhoshlk]

Dear Friends.

Pls help me to develop my below macro.
I have a workbook with 2 sheets, say "Entry" and "List"
"Entry" sheet has the following cells B6 and D7.
What I need is a command button in Entry Sheet, once I enter any data in B6
and D7 and if I click the command button macro should copy the cell "Entry(B6)" and paste value in cell "List(A1)' also copy cell "Entry(D7)" and paste value in cell "List(B2)".
a msg box to be appear and say "Your entry has updated" and the sheet "Entry" to be active and cells are cleared for the next new entry.

IMPORTANT is whenever I update the entry by clicking the command, the data should be copied in sheet "List" in next row so I keep the entry records in that sheet.

Please help.

Thanks in advance. S

 

[My Aswer Is This]

Try this:

Sub Paste_My_Data()
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("list").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowa = Sheets("List").Cells(Rows.Count, "B").End(xlUp).Row + 1
Sheets("List").Range("A" & Lastrow).Value = Sheets("Entry").Range("B6").Value
Sheets("List").Range("B" & Lastrowa).Value = Sheets("Entry").Range("D7").Value
Sheets("Entry").Range("B6").ClearContents
Sheets("Entry").Range("D7").ClearContents
MsgBox "Your entry has updated"
End Sub

 

[santhoshlk]

Try this:

Sub Paste_My_Data()
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("list").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowa = Sheets("List").Cells(Rows.Count, "B").End(xlUp).Row + 1
Sheets("List").Range("A" & Lastrow).Value = Sheets("Entry").Range("B6").Value
Sheets("List").Range("B" & Lastrowa).Value = Sheets("Entry").Range("D7").Value
Sheets("Entry").Range("B6").ClearContents
Sheets("Entry").Range("D7").ClearContents
MsgBox "Your entry has updated"
End Sub

Wow... It works perfectly... thanks a lot Bro

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Wow... It works perfectly... thanks a lot Bro

 

[santhoshlk]

Dears,

Can you pls help in below code:

Sheets("Form").Range("B8").ClearContents

here I need to clear the contents of sheet "form" cell A21 as wel.

Thanks

 

[My Aswer Is This]

Try this:

You should try reading the code and in time you should understand how to make additions and changes yourself.

You should see here I just added one more line of code.

I want in the long run to:
"Teach You To Fish" and not just give you a "Fish".

Sub Paste_My_Data()
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("list").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowa = Sheets("List").Cells(Rows.Count, "B").End(xlUp).Row + 1
Sheets("List").Range("A" & Lastrow).Value = Sheets("Entry").Range("B6").Value
Sheets("List").Range("B" & Lastrowa).Value = Sheets("Entry").Range("D7").Value
Sheets("Entry").Range("B6").ClearContents
Sheets("Entry").Range("D7").ClearContents
Sheets("Entry").Range("A21").ClearContents
MsgBox "Your entry has updated"
End Sub

 

[santhoshlk]

Sir this is not working

 

[My Aswer Is This]

What's not working?

You said in post #3

Wow... It works perfectly... thanks a lot Bro

 

[santhoshlk]

Yes... Its working correctly.

The prob is when I aply the clear content code for cell A21, which is merged.
Is there any solution to clear the content in merged cells

Thanks in advance

 

[My Aswer Is This]

What cell is in the upper left corner of the Merged cells?
Change the script to refer to the cell in the upper left corner of the Merged cells.

We constantly tell people to not use merged cells they can cause you a lot of headaches.