copy result to a specific location
 

Hi,

I have a worksheet (PayCalc) that calculates a persons wages for a
particular project. All I enter is the Employees ID No.

Each time I change the ID No the Total figure changes - my problem is that I
want to keep the calculated figure for use in the future.

I have another sheet (EmpInfo) that has lots of info stored by ID No. how
can I copy the calculated figure to the Pay column in this worksheet based
on ID No? I don't particularly want to have to copy it manually each time.

Here is what I think needs to be done (but I could be wrong!)

Step 1 Copy the calculated figure

Step 2 Lookup the Row Number for the correct ID No in the EmpInfo worksheet

Step 3 Paste the value of the calculated figure in the Pay column of the Row
Number found in step 2

Is this possible? if so, how

Thanks

copy result to a specific location
 

You can change this as required :-

'-------------------------------------------
Dim MyValue As Variant
Dim FoundCell As Object
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim ToSheet As Worksheet
Dim ToRow As Long
'---------------------
Sub transfer_data()
'- select cell containing search value
'- and run this macro from there
Set ToSheet = Workbooks("Book1.xls").Worksheets("Sheet1"
'**amend**
Set FromSheet = ActiveSheet
'------------------
MyValue = ActiveCell.Value
FromRow = ActiveCell.Row
'------------------
'- **nb. set correct column to search
Set FoundCell = ToSheet.Columns(1).Find(MyValue, LookIn:=xlValues)
If FoundCell Is Nothing Then
MsgBox (MyValue & " not found.")
Else
ToRow = FoundCell.Row
'- transfer additional data. **Change column numbers a
required.
ToSheet.Cells(ToRow, 5).Value = FromSheet.Cells(FromRow
2).Value
ToSheet.Cells(ToRow, 6).Value = FromSheet.Cells(FromRow
3).Value
End If
End Sub
'------------------------------------------------

--
Message posted from http://www.ExcelForum.com

copy result to a specific location
 

Brilliant - works a treat

Thanks



"BrianB " wrote in message
...
You can change this as required :-

'-------------------------------------------
Dim MyValue As Variant
Dim FoundCell As Object
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim ToSheet As Worksheet
Dim ToRow As Long
'---------------------
Sub transfer_data()
'- select cell containing search value
'- and run this macro from there
Set ToSheet = Workbooks("Book1.xls").Worksheets("Sheet1")
'**amend**
Set FromSheet = ActiveSheet
'------------------
MyValue = ActiveCell.Value
FromRow = ActiveCell.Row
'------------------
'- **nb. set correct column to search
Set FoundCell = ToSheet.Columns(1).Find(MyValue, LookIn:=xlValues)
If FoundCell Is Nothing Then
MsgBox (MyValue & " not found.")
Else
ToRow = FoundCell.Row
'- transfer additional data. **Change column numbers as
required.
ToSheet.Cells(ToRow, 5).Value = FromSheet.Cells(FromRow,
2).Value
ToSheet.Cells(ToRow, 6).Value = FromSheet.Cells(FromRow,
3).Value
End If
End Sub
'-------------------------------------------------


---
Message posted from http://www.ExcelForum.com/