New to VBA Macros - Trying to do compare of two Workbooks

Hello,

I have a Workbook with four worksheets in it. The header row on each is numbers. I need to compare the header row on each worksheet against column A in whichever other Workbook I have open and hi-light the matching fields in yellow. I have started the macro by coding it to ask for and open the corresponding workbook with the required fields in it. Please note that the required fields file will be a different one each time, as each time I use the Compare Test file, I am using for different clients with different required fields. I have attached examples for you. Any assistance would be greatly appreciated.

Excel Compare Test.xlsmRequired Fields Test.xls

Since you are planning to use this macro for different clients with different required fields, would you prefer if the macro prompts to open the file containing the header row and then prompt to open the list of headers to be matched?

Hello,

Thank you very much for your response. That would be wonderful if the header file could be a different file each time also. I actually pull a file from a database and add the header row to it before doing the compare. I apologize, but I previously neglected to explain that I must format the file before I do the compare. I add a blank column to the beginning of sheet 1, copy columns A and B from sheet 1 and paste these columns over top of columns A and B in sheets 2, 3, 4. Then I insert a row at the top of each sheet and insert the numbered header row exactly as it is in the example file, with the numbering for sheets 2, 3, and 4 beginning in column C. Sometimes my data file only includes 1 or two rows of data, but could be up to thousands. I have recorded a Macro that does the initial formatting part for me, but if it could be included in the Macro so there is just one step that would be wonderful.

Also, I was just asked to see if I could have a row with the count blanks formula for the required fields that are hi-lighted inserted at the top. Sorry to make this so complicated. Again, any assistance is greatly appreciated, as I need to utilize this in the very near future.

Thank you.

Do the steps you mentioned in post 3 need to be done for each workbook?

You can provide me the code you already have and i can help you add it to the code i will be providing you.

Hello,

Again, thank you very much for your assistance. Yes, the steps mentioned in post 3 do need to be done for each workbook, as they are not formatted as needed when they are pulled from our database. I have included my code below. Please let me know if you need anything further.

Application.ScreenUpdating = False
'
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("A:B").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Sheets("Sheet3").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Sheet4").Select
    ActiveSheet.Paste
    Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select
    Sheets("Sheet1").Activate
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A1:B1").Select
    Selection.AutoFill Destination:=Rows("1:1"), Type:=xlFillDefault
    Sheets("Sheet2").Select
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "257"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "258"
    Range("C1:D1").Select
    Selection.AutoFill Destination:=Range("C1:IU1"), Type:=xlFillDefault
    Sheets("Sheet3").Select
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "510"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "511"
    Range("C1:D1").Select
    Selection.AutoFill Destination:=Range("C1:IU1"), Type:=xlFillDefault
    Sheets("Sheet4").Select
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "763"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "764"
    Range("C1:D1").Select
    Selection.AutoFill Destination:=Range("C1:AG1"), Type:=xlFillDefault
    Sheets("Sheet1").Select
Prompt user to select file of required fields
Dim Ref_WBook As String

   Ref_WBook = Application.GetOpenFilename("Excel File (*.xls), *.xls") 'Command to allow browse procedure

   If Ref_WBook <> "False" Then
        Workbooks.Open Ref_WBook
   End If

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE] before your code and [/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.