I don't know this is what you want, but try this one.

copy and paste the following code to your Main sheet's module.
Click cells in column A or B or enter data in Main sheet. then, data in
Main sheet would be copied in sheet2 in this case.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tmp As Range
Dim Destsh As Worksheet

Application.EnableEvents = False

'Data in Main sheet will be transfer to destsh
Set Destsh = Worksheets("Sheet2") 'Change to your sheet's name

On Error Resume Next
If Target.Value = "" Then
Application.EnableEvents = True
Exit Sub
End If

Set tmp = Destsh.Columns("A:B").Find(Target.Value, _
LookIn:=xlValues, lookat:=xlWhole)

On Error GoTo 0
If Not tmp Is Nothing Then
MsgBox Target.Value & ":already exist. Contact your branch manager"
Exit Sub
End If

Select Case Target.Column Mod 2

Case 0
Set tmp = Destsh.Cells(Cells.Rows.Count, "B").End(xlUp)
If tmp < "" Then
tmp.Offset(1, 0) = Target.Value
Else
tmp = Target.Value
End If

Case 1
Set tmp = Destsh.Cells(Cells.Rows.Count, "A").End(xlUp)
If tmp < "" Then
tmp.Offset(1, 0) = Target.Value
Else
tmp = Target.Value
End If

Case Else

End Select

Application.EnableEvents = True

End Sub

keiji


Vijay DSK wrote:
Yes ND Pard
You are right. But the thing is it should be done with the help of VBA and
the change is not time based. Hence what I am looking what ever new thing
appears in those cells, they should be copied and pasted in the other sheet.

And also if any duplicate entry appears it should give a messae box stating
that "Contact your branch manger".

Hope I am clear.

Harald:
I am looking for a excel sheet only, but not for the user forms.


"ND Pard" wrote:

Appears that all you need to do is a Copy followed by a Paste-Special |
Transpose

Good Luck.

"Vijay DSK" wrote:

Hi all,
Thanks once again for the help rendered by this forum.
Once again its time for me to ask an other question.

The question is
I have 2 excel sheets. In sheet1 (Name is Main), i have customer name in A1
and customer no in A2, B1 will carry the customer name and B2 will carry the
customer no.

What I am looking at is ........
Any entry punched in the cells (B1 & B2) should be copied on to sheet 2 of
A2 and B2. Like wise any value punched in those cells should be copied on to
A3 and B3 and so on....
An other point is the vba should give a message "Contact Branch Manager" if
any duplicate entry is added.

Hope I am clear in asking the question.

Thanks once again.