Update pivot table from hidden sheets

[JFCLUK]

I have 8 data sources in separate sheets

I run a macro to consolidate all data into one sheet "MASTER"

I have a pivot table on sheet "REPORT"

The data sources are confidential. I want to hide these sheets and hide "MASTER"

When I hide the source sheets and "MASTER" the pivot table will not update I get a runtime error '1004' Select method of Worksheet class failed.

Ideally I'd like to make the source sheets and "MASTER" veryhidden.

Any help and guidance to resolve would be most appreciated.

 

[JFCLUK]

The challenge I'm having could stem from when I consolidate all of the data into "MASTER". I'm getting an error when the code reads the 5th line Sheets("Master").Select

Sub POPULATE_MASTER()

Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet

'DELETE OLD DATA FROM MASTER_NEW
Sheets("MASTER").Select
Cells.Select
'Range("A10000").Activate
Selection.Delete Shift:=xlUp
Range("A1").Select
'COPY HEADERS FROM HEADERS_NEW

Worksheets("HEADERS").Activate

Rows("1:2").Select

Selection.Copy

Worksheets("MASTER").Activate

ActiveSheet.Paste Destination:=Worksheets("MASTER").Range("A1")

Columns("A:A").Select
Application.CutCopyMode = False

'GET DATA FROM ALL OHER DEPARTMENT TABS


Worksheets("SOURCE1").Activate
Range("a8").Select
On Error Resume Next
Set mylastcell = Cells(1, 1).SpecialCells(xlLastCell)
mylastcelladd = Cells(mylastcell.Row, mylastcell.Column).Address
myrange = "a8:" & mylastcelladd
Range(myrange).Select
Selection.Copy

Worksheets("MASTER").Activate

Range("A" & Rows.Count).End(xlUp).Offset(1).Select

Selection.PasteSpecial xlPasteValues