Retreiving Sheet1 Data Autometically Uploaded in Sheet2

[Akash]

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" again to avoid .Areas
processing
End If
End Sub

Thanks

Akash

 

[Incidental]

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

 

[Guest]

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" again to avoid .Areas
MsgBox ("processing")
End If
ErrorReturn:
Application.EnableEvents = True

End Sub