VBA - search multiple cells in workbook 1 against multiple sheets in workbook 2, and paste matches in workbook 2

[cwclarke]

Hi, I'm completely new to macros and sadly all my success to date is via Google, trial and much error. So I'd be grateful for anyone's assistance. The situation is this:
I have a worksheet in workbook 1 that contains names and phone numbers split across different columns. I have workbook 2 that has up to 25 different worksheets that change daily (although the workbook, worksheet names and the columns remain constant) that I need to cross reference against the worksheet in workbook 1. If there are any matches then I need to copy and paste those matches in to workbook 2.
I'm so stuck I won't waste time by presenting any code on this post. Any advice would be greatly appreciated, thanks in advance.

 

[mumps]

Are the names and phone numbers in all the sheets in both workbooks in columns A and B respectively? What is the name of the sheet in workbook1? What is the name of workbook2 including extension? Are there any duplicate names in the name column in any sheet? I assume that by "matches", you are referring to the names. Is this correct?

 

[cwclarke]

Are the names and phone numbers in all the sheets in both workbooks in columns A and B respectively? What is the name of the sheet in workbook1? What is the name of workbook2 including extension? Are there any duplicate names in the name column in any sheet? I assume that by "matches", you are referring to the names. Is this correct?

Hi, thanks for coming back to me on this.
The names and phone numbers in each worksheet in both workbooks appear in different columns, but those columns locations remain constant one day after the next. If necessary I could specify each worksheet and the columns the data appears as this wouldn't change.
The name of the worksheet in workbook 1 is 'Members'.
Workbook 2 is named 'ALL dd.mm.yyyy.xlsx' The workbook name changes each day according to the date, which is determined by cell A3 on 'FrontSheet' in workbook 2.
Where one phone number appears in both workbooks then I want that phone number, along with the name associated with it, to appear on 'FrontSheet' in workbook 2.
Thanks.

 

[mumps]

Just to confirm, you want to compare phone numbers, not names. Are the names and phone numbers in the "Members" sheet always in the same columns or can they also vary? If they are always in the same columns in this particular sheet, what are the columns? If they are not always in the same columns, what are the column headers in row 1 for each column? Where on the "FrontSheet" do you want the name and phone number to appear? Since you can have up to 25 different worksheets in workbook2, it would be impractical to keep track of all the different locations of the names and phone numbers. Do the two columns have the same headers in row 1 in each sheet? For example, are the column headers something like "Name" and "Phone Number" across all sheets?

 

[cwclarke]

Hi, yes, I want to compare phone numbers, not names. The names and phone numbers in the 'Members' sheet are always in the same columns and do not vary, names are in columns A & B, phone numbers in columns C,D & E. On the 'FrontSheet' the results should start in cell F15 (first name), G15 (surname), H15, I15 and J15 (phone numbers), and work down the rows from there when there are multiple matches. All of the 25 different worksheets have unique column headers. Whilst some are the same (such as phone number), they aren't under the same column (i.e. one sheet it phone number could be column G, another it could be column AV).

 

[mumps]

It would appear that there are more factors that come into play such as not one column with phone numbers but 3 columns. It would make things more clear if you could post screen shots of what your data looks like in the "Members" sheet and one or two sheets of workbook2? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of both workbooks to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.