Search code examples
excelmergeautomationexcel-2013vba

Making a master file

I have made this macro to combine 4 different call trackers into one master file. My problem is that its only pulling info into the Master Tracker from Call Tracker and the Call Tracker SS and only the cases sheet.

Not getting information from the Call Tracker Jess or Miri files at all nor is it populating the other tabs: Tasks, Notifications, Special Requests or Follow Up, from all the files into the Master Tracker.

The weird thing is that I don't get any error. the Macro runs imports the first two records in the Call Tracker and Call Tracker SS Cases file and stops...

Could someone please review the code and advise what I may be doing wrong?

Sub Master_Tracker()

Dim wbMaster As Workbook
Dim wsCases As Worksheet
Dim wsTasks As Worksheet
Dim wsNotifications As Worksheet
Dim wsSpecialRequests As Worksheet
Dim wsFollowUp As Worksheet
Dim wbCall As Workbook
Dim wsSheet As Worksheet
Dim wbCallTrackerSS As Workbook
Dim wbCallTrackerJess As Workbook
Dim wbCallTrackerMiri As Workbook
Dim wsShCalls As Worksheet

Set wbMaster = Workbooks("Master Tracker.xlsm")
Set wsMaster = wbMaster.Sheets("Cases")
wsMaster.Cells.ClearContents

Set wbCall = Workbooks.Open(Filename:="H:\BX-HR\BX-INDUSTRIAL RELATIONS\HR REPRESENTATIVES\PRIVATE\HRSSC\US &CA HD\Call Tracker.xlsm")
If Not wbCall Is Nothing Then
  Set wsSheet = wbCall.Sheets("Cases")
  wsSheet.Range("A6:P" & wsSheet.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A3")
Else
  Exit Sub
End If

Set wbCallTrackerSS = Workbooks.Open(Filename:="H:\BX-HR\BX-INDUSTRIAL RELATIONS\HR REPRESENTATIVES\PRIVATE\HRSSC\US &CA HD\Call Tracker SS.xlsm")
If Not wbCallTrackerSS Is Nothing Then
  Set wsShCalls = wbCallTrackerSS.Sheets("Cases")
  wsShCalls.Range("A7:P" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Else
  Exit Sub
End If

Set wbCallTrackerJG = Workbooks.Open(Filename:="H:\BX-HR\BX-INDUSTRIAL RELATIONS\HR REPRESENTATIVES\PRIVATE\HRSSC\US &CA HD\Call Tracker Jess.xlsm")
If Not wbCallTrackerJess Is Nothing Then
  Set wsShCalls = wbCallTrackerJess.Sheets("Cases")
  wsShCalls.Range("A7:P" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Else
  Exit Sub
End If

Set wbCallTrackerMV = Workbooks.Open(Filename:="H:\BX-HR\BX-INDUSTRIAL RELATIONS\HR REPRESENTATIVES\PRIVATE\HRSSC\US &CA HD\Call Tracker Miri.xlsm")
If Not wbCallTrackerMiri Is Nothing Then
  Set wsShCalls = wbCallTrackerMiri.Sheets("Cases")
  wsShCalls.Range("A7:P" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Else
  Exit Sub
End If

wsMaster.AutoFilterMode = False
wsMaster.Rows("3:3").AutoFilter
With wsMaster.AutoFilter.Sort
  .SortFields.Clear
  .SortFields.Add Key:=Range("A3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With

Set wsMaster = wbMaster.Sheets("Tasks")
wsMaster.Cells.ClearContents

Set wsSheet = wbCall.Sheets("Tasks")
wsSheet.Range("A1:I" & wsSheet.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A1")
wsMaster.Range("A1:I1").EntireColumn.AutoFit

Set wsShCalls = wbCallTrackerSS.Sheets("Tasks")
wsShCalls.Range("A2:I" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Set wsShCalls = wbCallTrackerJess.Sheets("Tasks")
wsShCalls.Range("A2:I" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Set wsShCalls = wbCallTrackerMiri.Sheets("Tasks")
wsShCalls.Range("A2:I" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

wsMaster.AutoFilterMode = False
wsMaster.Rows("1:1").AutoFilter
With wsMaster.AutoFilter.Sort
  .SortFields.Clear
  .SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With

Set wsMaster = wbMaster.Sheets("Notifications")
wsMaster.Cells.ClearContents

Set wsSheet = wbCall.Sheets("Notifications")
wsSheet.Range("A1:I" & wsSheet.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A1")
wsMaster.Range("A1:I1").EntireColumn.AutoFit

Set wsShCalls = wbCallTrackerSS.Sheets("Notifications")
wsShCalls.Range("A2:I" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Set wsShCalls = wbCallTrackerJess.Sheets("Notifications")
wsShCalls.Range("A2:I" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Set wsShCalls = wbCallTrackerMiri.Sheets("Notifications")
wsShCalls.Range("A2:I" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

wsMaster.AutoFilterMode = False
wsMaster.Rows("1:1").AutoFilter
With wsMaster.AutoFilter.Sort
  .SortFields.Clear
  .SortFields.Add Key:=Range("D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With

Set wsMaster = wbMaster.Sheets("Special Requests")
wsMaster.Cells.ClearContents

Set wsSheet = wbCall.Sheets("Special Requests")
wsSheet.Range("A1:E" & wsSheet.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A1")
wsMaster.Range("A1:E1").EntireColumn.AutoFit

Set wsShCalls = wbCallTrackerSS.Sheets("Special Requests")
wsShCalls.Range("A2:E" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Set wsShCalls = wbCallTrackerJess.Sheets("Special Requests")
wsShCalls.Range("A2:E" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Set wsShCalls = wbCallTrackerMiri.Sheets("Special Requests")
wsShCalls.Range("A2:E" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

wsMaster.AutoFilterMode = False
wsMaster.Rows("1:1").AutoFilter
With wsMaster.AutoFilter.Sort
  .SortFields.Clear
  .SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With

Set wsMaster = wbMaster.Sheets("Follow Up")
wsMaster.Cells.ClearContents

Set wsSheet = wbCall.Sheets("Follow Up")
wsSheet.Range("A1:F" & wsSheet.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A1")
wsMaster.Range("A1:F1").EntireColumn.AutoFit

Set wsShCalls = wbCallTrackerSS.Sheets("Follow Up")
wsShCalls.Range("A2:F" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Set wsShCalls = wbCallTrackerJess.Sheets("Follow Up")
wsShCalls.Range("A2:F" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Set wsShCalls = wbCallTrackerMiri.Sheets("Follow Up")
wsShCalls.Range("A2:F" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

wsMaster.AutoFilterMode = False
wsMaster.Rows("1:1").AutoFilter
With wsMaster.AutoFilter.Sort
  .SortFields.Clear
  .SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With
Application.CutCopyMode = False

wbCallTrackerSS.Close False
wbCallTrackerJess.Close False
wbCallTrackerMiri.Close False
wbCall.Close False

Set wsShCalls = Nothing
Set wbCallTrackerSS = Nothing
Set wbCallTrackerJess = Nothing
Set wbCallTrackerMiri = Nothing
Set wsSheet = Nothing
Set wbCall = Nothing

Set wsMaster = Nothing
Set wbMaster = Nothing
End Sub
Solution

  • Start by adding Option Explicit at the top of your module and re-running your code.

    Eg:

    Set wbCallTrackerJG = Workbooks.Open(Filename:="H:\BX-HR\BX-INDUSTRIAL RELATIONS\HR REPRESENTATIVES\PRIVATE\HRSSC\US &CA HD\Call Tracker Jess.xlsm")
If Not wbCallTrackerJess Is Nothing Then
  Set wsShCalls = wbCallTrackerJess.Sheets("Cases")
  wsShCalls.Range("A7:P" & wsShCalls.Range("A" & Rows.Count).End(xlUp).Row).Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Else
  Exit Sub
End If

    Here you're opening a workbook and setting to wbCallTrackerJG (an un-declared variable), but then checking the status of wbCallTrackerJess (which is declared). Option Explicit would have hilighted that error for you.

    Note also that if your Open() fails then your code will raise an error, so checking for Nothing won't help you handle that case.