Sub CombiningSheets()
'Assumptions:
'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
Else
Worksheets(shMaster).Activate 'Otherwise, show the Master Sheet
End If
'**************************
'*End Define sheet to view*
'**************************
End Sub
