• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vba Vlookup between two workbooks (historical versions of each other) with multiple worksheets

PGJS

New Member
Hi

I wish to do a index match between two workbooks. Workbook A (set as wba) is saved as Workbook B (set as wbb)

Workbook A then has all its sheets unprotected and the pivot tables on multiple sheets refreshed (using wba.refreshall), all comments are cleared at side of pivot tables on multiple sheets.

The pivot tables refresh brings in less or more data on Workbook A. Now I wish to lookup the any data that was there last week and bring back the comments from Workbook B into Workbook A, eg, looking up historical comments where there is a match from Workbook A Sheet 1 to Workbook B Sheet 1 and repeating for all the workbook sheets

Wish to loop through the sheets of Workbook A and bringing back any comments or mismatches (#N/As) from the equivalent sheets in Workbook B. The columns are exactly the same for all worksheets. VBA required, vlookup, drag down to last row and loop to repeat in the remaining sheets.

Without looping I would have to do a vba lookup against each sheet, one at a time. Hopefully there is a easier looping solution.

Something like below: -

NEED help especially with the index match in terms of referencing the workbooks, the line starting .formula

Item in column A, Comments in column I
----------------------------------------------------------------------------
Code:
Dim lastrow

For i = 1 To wba.Sheets.Count
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    wba.Sheets(i).Rows(lastrow + 1 & ":" & lastrow + 1000).Delete shift:=xlUp


With wba.Sheets(i).Range("I11:I" & lastrow)
.Formula = "=index(wbb.Sheets(i)!$N:$N,MATCH(wba.A11,wbb.Sheets(i)!$A:$A,0),1)"
.Value = .Value
End With

Next i
----------------------------------------------------------------------------

Hope you can help. thanks
 
Last edited by a moderator:

PGJS

New Member
Reposting here as moved on a little but getting Error 438 on .formula line below.

wba is current BWQ file

--------------------
Code:
Dim FName As String
FName = "BWQ_copy.xlsx"    -- filename of workbook wbb to which I am looking up comments

Dim lastrow
    lastrow = wba.Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To wba.Sheets.Count  

With wba.Sheets(i).Range("I11:I" & lastrow)
   .Formula = "=INDEX('[" & FName & "]" & Sheets(i) & "'!$L:$L,MATCH(A11,'[" & FName & "]" & Sheets(i) & "'!$A:$A,0),1)"
   .Value = .Value
End With

Next i
--------------------

Hope someone can help resolve issue
 
Last edited:
Top