copy result to a specific location

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

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
 
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
'------------------------------------------------
 
Back
Top