Can this be Done

[harshab6]

HI Experts,
IF I enter a number in particular cell say C1 of sheet 1 and then click a command button then the number enter in C1 should cut and paste the number in A2 Column of sheet 2 of same workbook again if I enter another number IN C1 and click a command button then that number should cut and paste in Column A3 in sheet 2 and this should follow on .Please let me know if you have any questions.

 

[Stu Dapples]

Short answer is yes!

You need to tell your code to select Sheet1 C1, Cut,
Select sheet 2, select cell A1, select down (the equivalent of ctrl/shift down), offset one row, paste....

Now, the code! Will have to tinker and get back to you.... Probably more likely someone with better coding skills than me will beat me to it but again, short answer is yes!

 

[TonyMJ]

My suggestion would be something like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$1" Then Exit Sub
Sheets("Sheet2").Range("A1").Offset(WorksheetFunction.CountA(Sheets("Sheet2").Range("A:A")), 0) = Target.Value
End Sub

This is added to the Sheet1 code to update every time C1 is changed but you could add to a button etc.. if you want

 

[Derek Brown]

My short answer would probably be "Why?".
Unless you mean "Sheet1" etc. as seen in the VBA Project, you may have to change the code if you rename a worksheet.
But did you mean the order that they appear in native Excel? If you moved a worksheet forward or backward, would that make a difference?

 

[Stu Dapples]

I know someone would beat me to it but I got there after a tinker!

<code>
Sub CopyPaste()
' Copy And Paste to Last Cell
Sheets("Sheet1").Select
Range("A1").Select
Selection.Cut
Sheets("Sheet2").Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Select

End Sub
</code>
Good Luck!

 

[harshab6]

Hi Guys,


Stu Dapples and Derek Brown thankyou for your quick reply.Well Derek you have Guesed it right.Well Tony the code given by meets my creteria thanks.But when I give several conditions it doesnot work. eg

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$1" Then Exit Sub
Sheets("Sheet2").Range("A1").Offset(WorksheetFunction.CountA(Sheets("Sheet2").Range("A:A")), 0) = Target.Value
If Target.Address <> "$C$8" Then Exit Sub
Sheets("Sheet2").Range("B1").Offset(WorksheetFunction.CountA(Sheets("Sheet2").Range("B:B")), 0) = Target.Value


If Target.Address <> "$C$12" Then Exit Sub
Sheets("Sheet2").Range("C1").Offset(WorksheetFunction.CountA(Sheets("Sheet2").Range("C:C")), 0) = Target.Value

end sub

How to proceed with this.