How do I transfer some data to anther Wksheet based on matching ref no

[2ks]

I am trying to produce a macro that will match a ref number (in specific cell on the worksheetA) to another separate (workbook (Data) and specific sheet (main)).

when the match is found it will transfer data from specific cells on WorksheetA to corresponding cells in the other Workbook,Sheet(main).

Worksheet A is laid out as a form rather than linear and holds dets based on individual record only.

For example on WorksheetA the refernce number to base search on is in cell A6. Cell D6, E7, E10 have varied inputs from pull downs that the results need to be transferred to main sheet in new workbook. There are a few more than these 3 but I shoudl get the picture from any help with these three as I guess it just replicates

Then on Workbook, Sheet(main) it holds a list of all ref nos in Column A:A to base the search and match on.

When the ref no is matched in column A:A I need to activate the row on main sheet and trans over the above mentioned cells from WorksheetA to column F, J, L on the active row (Worksheet- Main).

Sorry bit garbled but hopefully will get picture. Huge thanks for help with this one

I cannot use VLOOKUP for this as the data in Worksheet A is not laid out in direct colums or row due to the nature of the form

 

[PHV]

What have you tried so far ?
You may consider the Find method of the Range object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[2ks]

I have no idea where to start on this part of my process.

I know how to select the cell range on Work Sheet A but dont know how to get it to look in the other Worksheet although i guess it will be something like A:A.

Then I need it to activate just that row and transfer data back from the Worksheet A cells to the other worksheet.

Most of my macro work has been with the recorder to date and although I can get users to double entry as work around I would rather automate.

Any coding ideas?

Thanks

 

[PHV]

A starting point:
With yourFormWB.Sheets("A")
Set c = yourDataWB.Sheets("main").Range("A:A").Find(.[A6], LookIn:=xlValues)
If c Is Nothing Then
MsgBox "refernce number " & .[A6] & " not found"
Exit Sub
End If
c.Offset(0, 5) = .[D6]
c.Offset(0, 9) = .[E7]
End With c.Offset(0, 11) = .[E10]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[2ks]

I will owe you a crate of beer by looks of things

I will test out in the morning as late now and need to crash.

Will def let you know outcome

Ta muchly

 

[PHV]

Just see a typo:
With yourFormWB.Sheets("A")
Set c = yourDataWB.Sheets("main").Range("A:A").Find(.[A6], LookIn:=xlValues)
If c Is Nothing Then
MsgBox "refernce number " & .[A6] & " not found"
Exit Sub
End If
c.Offset(0, 5) = .[D6]
c.Offset(0, 9) = .[E7]
c.Offset(0, 11) = .[E10]
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[2ks]

I have used your coding but must be missing something as return err

This is what I have used, bearing in mind a few changes to size of sheets the cell ref and names of sheets have now changed.

Cheers again if you can help

Sub TRNS_OUT_TO_AMF()
'
' TRNS_OUT_TO_AMF Macro
' Macro recorded 28/02/2007 by Nikk Watt


With Workbook1005.Sheets("A")
Set c = data.Sheets("Main").Range("A:A").Find(.[g10], LookIn:=xlValues)
If c Is Nothing Then
MsgBox "refernce number " & .[g10] & " not found"
Exit Sub
End If
c.Offset(0, 32) = .[i94]
c.Offset(0, 31) = .[ac118]
c.Offset(0, 33) = .[ac119]
End With

End Sub

 

[2ks]

PHV

If you are able to could you please help me further with my problem

Merci

Nikk

 

[PHV]

Which error ? Where ?
What are Workbook1005, data ? workbook names or workbook objects ?
As you get an error, I guess you used names:
With Workbooks("Workbook1005").Sheets("A")
Set c = Workbooks("data").Sheets("Main").Range("A:A").Find(.[g10], LookIn:=xlValues)
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[2ks]

100% perfect and works a treat.

I will forever be extremely grateful beyond what words can ever ever say. People such as yourself are amazing individuals, helping us novices in times of need.

Will never be able to thank you enough.