Remove this close statement, otherwise, your workbook will close

MonthlyWbk.Close


"Joel" wrote:

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?