Bring Full Name to a sheet from another sheet

[Aliakbar]

Hi,

I have a short name in one sheet and a full name name in another sheet. how do i bring the full name next to the column of short name.

for example: short name "Fila" is in one sheet and full name "Fila Industrial Chemica SpA" is in another sheet. how do i bring "Fila Industrial Chemica SpA" next to the colum of "Fila". currently i'm doing copy and paste but the datas are more.

Please advise.

Thank you
Ali

 

[frank_AL]

If you will answer these questions I believe I can help you.

1. What is the name of the worksheet where the short name resides?
2. What is the column on that worksheet where the short name is and what column do you want the full name copied to?
3. What is the name of the worksheet where the full name resides?
2. What is the column on that worksheet where the full name is?

 

[mumps]

Assuming your short names are in column A starting at row 2 of Sheet1 and your full names are in column A of Sheet2, try the following macro:

Sub CopyName()
    Application.ScreenUpdating = False
    Dim Val As Range
    Dim foundVal As Range
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each Val In Sheets("Sheet1").Range("A2:A" & LastRow)
        Set foundVal = Sheets("Sheet2").Range("A:A").Find(Val, LookIn:=xlValues, lookat:=xlPart)
        If Not foundVal Is Nothing Then
            Val.Offset(0, 1) = foundVal
        End If
    Next Val
    Application.ScreenUpdating = True
End Sub

Change the sheet names and ranges to suit your needs.

 

[Aliakbar]

Thank You. it's worked

 

[mumps]

You are very welcome.

 

[Peter_SSs]

Just be aware that if there is no formal table/link between the short name and the full name you could get some unexpected results.
For example, with a short name of "Fila" in Sheet 1 and the below list of full names in Sheet 2, the result will be "Dubai Filament Co" not "Fila Industrial Chemica SpA"

Book1
	A
1
2	Some Company Name
3	Dubai Filament Co
4	Some Other Company
5	Fila Industrial Chemica SpA
Sheet2