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
----------------------------------------------------------------------------
----------------------------------------------------------------------------
Hope you can help. thanks
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: