Hello there,

I am having quite the predicament with my VBA solution for consolidating worksheets.

The situation: Due to the work processes here, each individual (6) would like to have a separate worksheet to enter in information for a defect on a form. Using this tally sheet they save the information in the sheet and close it at the end of their process. This workbook (6 of them in total) is on a SharePoint 2013 site under a library (similar to a network drive, kinda). I have the 6 workbooks consolidated into a "Master" workbook that creates an actual copy of each worksheet and places it within.

Now from here, I have the following VBA to grab each Table and its information to compile it into one list under another worksheet titled "Summary." Here is the VBA inserted in the module:

-----
Option Explicit

Sub Consolidate()
Dim NR As Long, ws As Worksheet, wsA As Worksheet
If MsgBox("Create Summary Report from all worksheets?", _
vbYesNo + vbQuestion) = vbYes Then
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Application.EnableEvents = False

Sheets("Summary").Activate
Set wsA = ActiveSheet
Range("A2", Range("A2").SpecialCells(xlCellTypeLastCell)).ClearContents
NR = 2

For Each ws In Sheets(Array("Barb", "Cecilia", "Gus", "Mary", "Yi", "Vacant"))
ws.Activate
Range("A2", Range("A2").SpecialCells(xlCellTypeLastCell)).Copy wsA.Range("A" & NR)
NR = wsA.Range("A1").End(xlDown).Row + 1
Next ws
End If
wsA.Activate
Set wsA = Nothing
ResetAll:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub

ErrorHandler:
MsgBox Err.Number & " - " & Erl & " - " & Err.Description
Resume ResetAll

End Sub

-----

This code works; to a degree I believe

ISSUE: When I have the following VBA for when the workbook opens it creates the table but when I refresh all the data, because I am using a pivot table and a COUNTA function for the "Summary" table, it breaks the linkages to the data and isn't necessarily operations friendly. If I were to solely use it the issues could just be worked around, but as you know, creating a workbook for a team really needs to be 'full-proof.'

Option Explicit
Private Sub Workbook_Open()

Application.OnTime Now() + TimeValue("00:00:03"), "Consolidate"
ThisWorkbook.RefreshAll

End Sub

'NOTE for excel help forum people, I have a delay of 3 seconds because it helps it run a little smoother, especially as this workbook is liked to others
----

If you could help me or guide me in anyway, I would greatly appreciate it. I am a relative newbie to VBA, so plain language and patience is always appreciated.

Regards,

Chris