How to merge worksheets into one with different values.


New Member
Feb 10, 2005
Hi I'm using Excel 2000,

I have this scenario.

I have a master worksheet.


12345 | ABC COMP | SML BUS |002222|88008|C5000|C5001 |
67890 | XYZ COMP | COMM |005555 |99009|K7000|K7001 |

I also have 5 data worksheets from different sources that contain
information relating to some of the columns in the master.
All with the ACCT# as the common identifier.


Data Worksheet 1

ACCT# | GCM# |

Data Worksheet 2

ACCT# | HC# |

etc, etc.

Is there a macro or formula to get the five data Worksheets to populate their corresponding columns in the master using the ACCT# column as the relationship?
Some of these data worksheets have lines into the thousands and can become hard to monitor. I'm trying to reduce the cut and paste time.

I would appreciate a step by step guide as I am a total Excel newbie.


Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

try this code out. even though it looks long on here, it is really pretty simple.

as you will note i had to make some assumptions, which i believe are clearly listed at the top of the code. per your request, i've also tried to document thoroughly what sections of the code are doing, and where appropriate, what specifically lines are doing.

i don't presume that this is the fastest method -- it took me about 2 min to run with 5000 rows and 6 sheets -- but it should definitely be better than doing it by hand!

hope this helps. ben.

ps. you actually don't need to use (this) code at all, if you know that each individual sheet contains all of the account numbers. in that situation, all you would need to do is do a sort on each worksheet -- if all of your worksheets have all of the account numbers, this would assure that row X on the master sheet contains the same account number as on all of the sub-sheets. Use a simple reference and you'd be done!

Sub CombiningSheets()

'1) Account# is in Column A for all worksheets
'2) The master sheet is entitled "Master Sheet" and contains all of the account numbers
'3) You wish to import data from all of the worksheets in the workbook (excepting the master sheet)
'4) The data you wish to bring in from the other sheets is located in Column B on the appropriate sheet.
'5) Column Headings are in Row 1 for all worksheets
'6) The data will be imported in the order of your sheets (Col B from Sheet2 -> Col B on Master Sheet, Col B from Sheet3 -> Col C on Master Sheet, etc.)

'*Variable Declarations.*
Dim shCount As Integer, rwCount As Long 'Define shCount as an integer and rwCount as a long.  These variables will give the number of sheets in the workbook and rows in the Master Sheet, respectively
Dim sh As Integer, rw As Long 'Define sh as an integer and rw as a long.  These variables will track the sheet and row number, respectively.
Dim cAcctNmr As Range, rngAcctNmr As Range 'Defines cAcctNmr and rngAcctNmr as ranges
Dim errorCount As Long 'Define errorCount as a long.  errorCount will track any missing account numbers
Dim AcctNmr As Variant 'Defines AcctNmr as a Variants.  AcctNmr will be used to match Account Numbers across sheets
Dim shMaster As String 'Defines shMaster as a String.  shMaster will be "Master Sheet"
'*End of Variable Declarations*

'*Variable Initialization*
shCount = ActiveWorkbook.Worksheets.Count 'Counts number of worksheets in the workbook
shMaster = "Master Sheet" 'Sets the variable shMaster equal to the name of the master sheet (assumed to be "Master Sheet")
Set rngAcctNmr = Worksheets(shMaster).Range("A:A").SpecialCells(xlCellTypeConstants) 'Selects a range containing all account numbers on the master sheet
rwCount = rngAcctNmr.Rows.Count 'Counts the number of rows of account numbers
'*End of Variable Initialization*

'*Checks worksheet format*
Application.DisplayAlerts = False 'Suppress delete alert
Worksheets(shMaster).Move before:=Worksheets(1) 'Makes sure the first sheet is the Master Sheet
If Worksheets(shCount).Name = "Missing Account Numbers" Then 'If the worksheet "Missing Account Numbers" exists...
    Worksheets(shCount).Delete '...Then delete it and...
    shCount = shCount - 1 'Adjust the worksheet count appropriately.
End If
Application.DisplayAlerts = True 'Allow alerts
'*End of Format Checking*

Application.ScreenUpdating = False 'Does not update the screen if changes are made

'*Merging data from sheets onto Master Sheet*
rw = 1 'Sets the variable rw equal to 1 to account for the column headings.
Do 'Loop to go through the rows
    rw = rw + 1 'Moves to the next row
    AcctNmr = Worksheets(shMaster).Cells(rw, 1) 'Sets the Account Number
    sh = 1 'Sets the variable sh equal to 1 to account for the master sheet
    Do 'Loop to go through the sheets
        sh = sh + 1 'Moves to the next worksheet
        With Worksheets(sh).Range("A:A") 'Looking at the cells containing the account numbers
            Set cAcctNmr = .Find(AcctNmr, LookIn:=xlValues) '...Find the Account Number contained in the cell cAcctNmr
            If Not cAcctNmr Is Nothing Then 'If the Account Number is found...
                Worksheets(shMaster).Cells(rw, sh) = cAcctNmr.Offset(rowOffset:=0, columnOffset:=1).Value '...Then copy it to the Master Sheet.
            Else 'Otherwise...
                errorCount = errorCount + 1 'Tracks the number of errors
                If errorCount = 1 Then 'If it is the first error...
                    Worksheets.Add.Name = "Missing Account Numbers" 'Create a worksheet called "Missing Account Numbers"
                    With Worksheets("Missing Account Numbers")
                        .Cells(errorCount, 1) = "Account Number" 'Add the column heading
                        .Cells(errorCount, 2) = "Sheet Name" 'Add the column heading
                        .Move after:=Worksheets(shCount + 1) 'Move this worksheet to the end of the workbook'
                    End With
                End If
                With Worksheets("Missing Account Numbers") 'With "Missing Account Numbers"
                    .Cells(errorCount + 1, 1) = AcctNmr 'Copy the Missing Account Number
                    .Cells(errorCount + 1, 2) = Worksheets(sh).Name 'Copy the name of the worksheet which does not contain the account number
                End With
            End If
        End With 'Stop working with the cells containing the account numbers
    Loop While sh < shCount 'Loop until all sheets have been checked
Loop While rw < rwCount 'Loop until all rows have been checked
'*End Merging data from sheets onto Master Sheet*

Application.ScreenUpdating = True 'Shows changes made on-screen

'*Define sheet to view after program ends*
If errorCount > 0 Then 'If there were errors...
    Worksheets("Missing Account Numbers").Activate 'Then show the log sheet
    Worksheets(shMaster).Activate 'Otherwise, show the Master Sheet
End If
'*End Define sheet to view*

End Sub
Upvote 0
Thanks Ben


Thanks for the help. I had a similar reply by Just_Jon. I will try both codes out to see which is more suitable.

I'll let you know if it works.

Thanks again. :wink:
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back