Thank you and yes, I have already changed the row1 back to long and removed the set row1 = statement. Maybe it is the 3rd sheet that is causing the problem? I have an open workbook called "Approve PO" where the code resides. Then I have another sheet open that is called "Manual PO with a variable date", lastly I am opening another sheet called "Manual PO log" where I want to put a status code of "A". I have attached sheets and below is the changes I have made to try and get this to work, unsuccessfully.
Sub Update_Log_Status()
Dim wrkbk1 As Workbook
Dim sht1 As Worksheet
Dim row1 As Long
Dim mywb As Workbook
Dim sht2 As Worksheet
'Path to closed file
Const filePath1 As String = "J:\Manual POs\Manual PO Log\Manual PO Log2.xlsx"
'Stop updating the screen
Application.ScreenUpdating = True
Set mywb = ActiveWorkbook
'Open the workbook
Set wrkbk1 = Workbooks.Open(filePath1)
'Grab the first sheet in wrkbk
Set sht1 = wrkbk1.Sheets(1)
mywb.Activate
Set sht2 = ActiveWorkbook.Sheets("PO")
'Find value in B21 of active worksheet in colum D of closed workbook
row1 = sht1.Range("B:B").Find(sht2.Range("B21").Value, , xlValues, xlWhole).row
sht1.Cells(row1, 10).Value = "A"
'Close workbook & save
wrkbk1.Close True
'Set the screen to update
Application.ScreenUpdating = True
End Sub
|