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.

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.

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.

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.

I used your code and modified it a bit as follows:

Sub replacenames()

Const MasterSheet = "C:\Reports\DNLD\[HS_Monthly.xls]HS_Monthly!" 'contains
long names
Const ShortNamesSheet = "C:\Reports\[Master Plant Names.xls]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("A9:A" & MasterLastrow)

For Each Plant_GSDB_Code In MasterRange

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

Now I'm getting an error "Subscript out of range" related to the line
starting near the top with "With Sheets(ShortNamesSheet)"

Any ideas?

VBA references aren't the same as the worksheet. I modified your changes.
You also have to open workbooks in VBA where in worksheets you can just
reference the workbooks without openning.

the workbook where the code is running is called Thisworkbook.



Sub replacenames()

Const MonthlyWbkName = "C:\Reports\DNLD\HS_Monthly.xls"
Const PlantNameWbkName = "C:\Reports\Master Plant Names.xls"
Const MasterSheet = "HS_Monthly" 'contains long names
Const ShortNamesSheet = "Sheet1!"

Set MonthlyWbk = ThisWorkbook
Workbooks.Open Filename:=PlantNameWbkName
Set PlantNameWbk = ActiveWorkbook


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

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

For Each Plant_GSDB_Code In MasterRange

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

MonthlyWbk.Close
PlantNameWbk.Close
End Sub

"Jeff Gross" wrote:

I used your code and modified it a bit as follows:

Sub replacenames()

Const MasterSheet = "C:\Reports\DNLD\[HS_Monthly.xls]HS_Monthly!" 'contains
long names
Const ShortNamesSheet = "C:\Reports\[Master Plant Names.xls]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("A9:A" & MasterLastrow)

For Each Plant_GSDB_Code In MasterRange

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

Now I'm getting an error "Subscript out of range" related to the line
starting near the top with "With Sheets(ShortNamesSheet)"

Any ideas?