Consolidated workbook to VBA worksheet table merge followed by refresh data

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

Make your summary table a real Excel Table and use that as the data source for the pivot table. Once linked to an Excel Table, a pivot table becomes aware of the range no matter how many rows the Excel Table has, or how many columns for that matter.

Here's an introduction to Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel

To clear out the contents of the table before concatenating the subordinate sheets in, use the following code:

Please Login or Register  to view this content.

Thank you for the tip about having the pivot table reference the NAME of the table oppose to the range, simple and effective solution.

Regarding the VBA you provided me, it is not not working. I am having a Run Time Error 438 "Object does not support this property or method." I've tried searching the web for solutions and have come across the VBA.

I've removed the wsa.Activate in the later part of the VBA and it seems to be addressing my issue, but now I need it to return to the activesheet :|

Any ways, thank you.