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

 

[hyd1956]

The full path, should it be just file name?

 

[Fluff]

No, it should be the full path.
Which line of code gives the error?

 

[hyd1956]

Full path, I've just tested it within the same worksheet and it works fine. Just not across workbooks.

Across workbooks I'm using this - Not working

Dim lookUpSheet As Worksheet, updateSheet As Worksheet
Dim Accept As Workbook, wbk2 As Workbook
Dim valueToSearch As String
Dim i As Integer, t As Integer

Set Accept = ThisWorkbook
Set wbk2 = Workbooks.Open(Filename:="Folder\filename.xlsm", Password:="password", UpdateLinks:=1)
Set lookUpSheet = Accept.Worksheets("Notseen")
Set updateSheet = wbk2.Worksheets("Tracker")

Within the same workbook - Working

Private Sub CommandButton1_Click()

Dim lookUpSheet As Worksheet, updateSheet As Worksheet
Dim valueToSearch As String
Dim i As Integer, t As Integer

Set lookUpSheet = Worksheets("Notseen")
Set updateSheet = Worksheets("Sheet3")

 

[hyd1956]

Runtime error 9 - subscript out of range

 

[Fluff]

On which line of code do you get the error?

 

[hyd1956]

There is no debug option on the error, how else can I find that out?

 

[Fluff]

If you step through the code using F8, what line does it stop on?

 

[hyd1956]

Ah right, this has helped me solve the issue I've changed workbook names, sheet names and reference names. Working fine now. Thanks

 

[Fluff]

Glad you were able to solve it & thanks for the feedback.