I have been struggling with this for days and I have tried multiple ways of getting this task done but it's always buggy. Either I get an object error with the pastespecial method or I get the object variable error with value = value, referring to the row1. If I set row1 to a range, I get the to the open.workbooks and the code stops due to a built in function. I do not know enough to fix this and I would sure appreciate some help and understanding. I just want to copy a value from an open workbook then open a closed workbook, find the value, and add an "A" six cells to the right, then close the workbook. Here's the mess I have. Please ignore the variables with "2's" I am going to delete them.



Sub Update_Log_Status2()
Dim wrkbk1 As Workbook
Dim sht1 As Worksheet
Dim row1 As Range

'Path to closed file
Const filePath1 As String = "J:\Manual PO's\Manual PO Log\Manual PO Log.xlsx"


'Stop updating the screen
Application.ScreenUpdating = False
'
'Open the workbook
Set wrkbk1 = Workbooks.Open(filePath1)

'Grab the first sheet in wrkbk
Set sht1 = wrkbk1.Sheets(1)
Set row1 = ActiveSheet.Rows



'Find value in B21 of active worksheet in colum D of closed workbook
row1 = sht1.Range("D").Find(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

Your code is errant in the declaration of row1, it should be Long and the Set row1= line should be removed.

What workbook is your code in?

Your verbiage says to first copy a value from an open workbook.... I don't see any attempt to do this.
The B21 you're using is on the same sheet as you are looking for it in the just opened workbook.

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

Attached Files

	Manual PO 063869_B002507001 _ 07-Mar-17 13-21-59_2.xlsx (15.1 KB, 10 views)
	Approve PO2.xlsm (44.0 KB, 10 views)
	Manual PO Log2.xlsx (14.1 KB, 10 views)

This works for me.

Hi - Thank you but unfortunately it still has one of the issues I was dealing with. Any ideas? I attached some screen prints. I am testing it with F8 step through. I get to the highlighted line it flashes and the function macro opens, seemingly killing my macro and never opening the log.

Attached Files

Function Error.docx (444.7 KB, 9 views)

Afraid I don't know what you mean the function macro opens.

If you are referring to the debug dialogue...
The only way I get the macro to error on that line is by using the wrong file path and then the debug dialogue tells me what the problem is

Run-time error 1004
"J:\Manual POs\Manual PO Log\Manual PO Log2.xlsx" could not be found.

When I use the file path to the file on my own computer, which I know is right, there is no error.
My only guess is your file path is not right.

It's interesting because it works fi I run it but if I try and step through it, I get an error. Anyway, I am going to put this one to bed! Thank you so much everyone for your help