Hi,

I have a Querry regarding Macros.

I have Two Sheets
In Sheet 1 i have Three Columns

A1. Item Name
B1. Item Qty
C1. Item Price

In Sheet 2 i have 4 Columns

A1. Item Name
B1. Item Qty
C1. Item Price
D1. Calculations

Now i want that as soon as the user enters the Data in Sheet1 it
should autometically gets uploads in the Sheet2.

Right Now i am using the following Formula in Sheet2.
=TRIM('Sheet1'!A1)
=TRIM('Sheet1'!B1)
=TRIM('Sheet1'!C1)

I try the below mentioned code sugested by Nopik on 8th March 2007,
but its not working properly.

Can anyone help me in this regards

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 5 Then 'Assume, that you have 4 columns from A to D
Range(Worksheets("Sheet1").Range("A" & Format(Target.Row) & ":C" &
Format(Target.Row))).Copy Worksheets("Sheet2").Range("A" &
Format(Target.Row) & ":C" & Format(Target.Row)) 'You can use "A:C"
Range or, walk thru all .Areas
Range(Worksheets("Sheet2").Range("D" & Format(Target.Row))).Copy
Worksheets("Sheet1").Range("D" & Format(Target.Row)) 'If you want to
return your calculations in Column D. Use "D:D" again to avoid .Areas
processing
End If
End Sub

Thanks

Akash

Hi Akash

You can try this code it should hopefully work for you. open VBE or
press Alt and F11 then in the project explorer pane double click sheet
1 and then paste the following code into the module

Option Explicit
Dim MyStr As String

Private Sub Worksheet_Change(ByVal Target As Range)
MyStr = Target.Address
Range(MyStr).Copy
Sheets(2).Range(MyStr).PasteSpecial
Application.CutCopyMode = False
End Sub

Hope this is of some help to you

S

I modified your code to get ride of the errors

Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ErrorReturn
If Target.Column < 5 Then
'Assume, that you have 4 columns from A to D
MyRange = "A" & Format(Target.Row) & ":C" & Format(Target.Row)
Worksheets("Sheet1").Range(MyRange).Copy _
Destination:=Worksheets("Sheet2"). _
Range("A" & Format(Target.Row))
'You can use "A:C" Range or, walk thru all .Areas
Worksheets("Sheet2").Range("D" & Format(Target.Row)).Copy _
Destination:=Worksheets("Sheet1"). _
Range("D" & Format(Target.Row))
'If you want to return your calculations in Column D.
'Use "D:D" again to avoid .Areas
MsgBox ("processing")
End If
ErrorReturn:
Application.EnableEvents = True

End Sub


"Akash" wrote:

Hi,

I have a Querry regarding Macros.

I have Two Sheets
In Sheet 1 i have Three Columns

A1. Item Name
B1. Item Qty
C1. Item Price

In Sheet 2 i have 4 Columns

A1. Item Name
B1. Item Qty
C1. Item Price
D1. Calculations

Now i want that as soon as the user enters the Data in Sheet1 it
should autometically gets uploads in the Sheet2.

Right Now i am using the following Formula in Sheet2.
=TRIM('Sheet1'!A1)
=TRIM('Sheet1'!B1)
=TRIM('Sheet1'!C1)

I try the below mentioned code sugested by Nopik on 8th March 2007,
but its not working properly.

Can anyone help me in this regards

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 5 Then 'Assume, that you have 4 columns from A to D
Range(Worksheets("Sheet1").Range("A" & Format(Target.Row) & ":C" &
Format(Target.Row))).Copy Worksheets("Sheet2").Range("A" &
Format(Target.Row) & ":C" & Format(Target.Row)) 'You can use "A:C"
Range or, walk thru all .Areas
Range(Worksheets("Sheet2").Range("D" & Format(Target.Row))).Copy
Worksheets("Sheet1").Range("D" & Format(Target.Row)) 'If you want to
return your calculations in Column D. Use "D:D" again to avoid .Areas
processing
End If
End Sub

Thanks

Akash