Transferring to another workbook

[hyd1956]

Hi

I'm trying to transfer a row over to it's matching row in another worksheet so that the corresponding information is updated. I'm using the following code but its not working, can you see where I am going wrong or suggest something different?

Thanks


Sub Confirmtransfer()
Application.ScreenUpdating = False
Dim lookUpSheet As Worksheet, updateSheet As Worksheet
Dim Accept As Workbook, wbk As Workbook
Dim valueToSearch As String
Dim i As Integer, t As Integer

Set Accept = ThisWorkbook
Set wbk = Workbooks.Open(Filename:="Filename.xlsm", Password:="password", UpdateLinks:=1)
Set updateSheet = wbk.Worksheets("Tracker")
Set lookUpSheet = Accept.Worksheets("Hidden Accept")

lastRowLookup = lookUpSheet.Cells(Rows.Count, "D").End(xlUp).Row
lastRowUpdate = updateSheet.Cells(Rows.Count, "I").End(xlUp).Row

For i = 1 To lastRowUpdate
valueToSearch = updateSheet.Cells(i, 9)
For t = 1 To lastRowLookup
If lookUpSheet.Cells(t, 1) = valueToSearch Then
updateSheet.Cells(i, 20) = lookUpSheet.Cells(t, 6)
updateSheet.Cells(i, 21) = lookUpSheet.Cells(t, 7)
updateSheet.Cells(i, 26) = lookUpSheet.Cells(t, 7)
updateSheet.Cells(i, 22) = lookUpSheet.Cells(t, 8)
updateSheet.Cells(i, 25) = lookUpSheet.Cells(t, 9)
updateSheet.Cells(i, 28) = lookUpSheet.Cells(t, 10)
updateSheet.Cells(i, 24) = lookUpSheet.Cells(t, 11)
updateSheet.Cells(i, 27) = lookUpSheet.Cells(t, 13)
updateSheet.Cells(i, 29) = lookUpSheet.Cells(t, 13)
updateSheet.Cells(i, 39) = lookUpSheet.Cells(t, 14)

Exit For
End If
Next t
Next i
Application.ScreenUpdating = True
End Sub

 

[Fluff]

In what way doesn't it work?

 

[hyd1956]

Debug takes me to
Set updateSheet = wbk.Worksheets("Tracker")

Am I opening / referencing the worksheet incorrectly? It seems to work fine when I use the code between worksheets and not across workbooks.

Thanks

 

[Fluff]

Does the other workbook open?
If so what is the error message & number?

 

[hyd1956]

Error is Run time error 9 - Subscript out of range.

I changed Set updateSheet = wbk.Worksheets("Tracker") to updateSheet = Worksheets("Tracker") which removes the error, but it doesn't copy any of the data over when I do that

 

[Fluff]

Are you trying to copy to or from the Tracker sheet?
Also you didn't answer my question on if the other workbook opened.

 

[hyd1956]

Sorry, yes it is opening and I want to copy to the Tracker sheet

 

[Fluff]

In that case it sounds like the sheet name is wrong, check that there are no leading/trailing spaces.
You also need to add the .Value to these lines, like

 updateSheet.Cells(i, 20).Value = lookUpSheet.Cells(t, 6).Value

 

[hyd1956]

Hmm odd, today it's not opening the other workbook so maybe I was wrong on that and I'm getting a run time error of 1004. application defined or object defined error. I've checked for any spaces and it's not that, the file names and sheet names are all correct.

 

[Fluff]

Are you just using the filename, or the full path in the workbooks.open?