Changge the two constants a the beginning of the macro as necessary.

Sub replacenames()

Const MasterSheet = "Master" 'contains long names
Const ShortNamesSheet = "Sheet1"

With Sheets(ShortNamesSheet)
ShortLastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set ShortRange = .Range("A2:A" & ShortLastrow)
End With

With Sheets(MasterSheet)
MasterLastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set MasterRange = .Range("A2:A" & MasterLastrow)

For Each FacilityCode In MasterRange

Set c = ShortRange.Find(what:=FacilityCode, LookIn:=xlValues)
If Not c Is Nothing Then
FacilityCode.Offset(rowoffset:=0, columnoffset:=1) = _
c.Offset(rowoffset:=0, columnoffset:=1)
End If
Next FacilityCode
End With
End Sub

"Jeff Gross" wrote:

Spreadsheet 1 example data:

A B
Facility Name Facility Name
EC1AA Chicago
BD2EC Paris

Spreadsheet 2 example data:

A B
EC1AA Chicago Focused Factory
BD2EC Paris Center of Research and Study

Master Spreadsheet is where code is located behind a macro button that
completes data updating, query updating, chart updating (~1000 different
charts) for data and recoloring based on new data.

If you need more, I can provide.

Thanks.

Jeff




"Joel" wrote:

Your problem doesn't sound very complicated. except I need more details. It
would help to have sample data, columns, and worksheet names. The only
caveoat is how to translate long names to short names.. there may be a
little special coding to handle the translation.

"Jeff Gross" wrote:

Here is my situation:

First, I have a spreadsheet that has shortened names of ~ 100 facilities
along with a corresponding absolute reference code (number/letter mix). For
example, Chicago may have a code like GA123.

Second, I have a master spreadsheet that includes tons of programming with
charts and sorts.

Third, each month I need to pull a downloaded file (spreadsheet 3) from an
external source which has data associated with the facilities. The external
souce uses very long names sometimes, so my master spreadsheet has some
coding that copies the shortened versions from spreadsheet 1 into spreadsheet
3.

My problem has started recently when a few facilities were sold and a few
were bought.

I would like my master spreadsheet to look at the plants listed in
spreadsheet 3 and then match them by code with spreadsheet 1, and then
replace the long version names in spreadsheet 3. If there is a plant change,
I would like for the spreadsheet 1 to be updated automatically with the name
and code from spreadsheet 3.

I know it's a bit confusing, so if you need more information, I can provide.

thanks.