extract information from various worksheet

[ghan7650]

Hi,

Right now, I have the vba code in place to extract the pivot tables from employees tab and display them in the master tab.
I am trying to extract the names employees (cell C3) from various tabs to the master tab and display them a cell above the pivot table on the master tab.

is this the right code to extract names?

' EmpName = ws.cells(c,3).Copy Destination:=worksheets("Master").cells(CellRef-1,1)

[ghan7650]

Any suggestions?

[arlu1201]

Change it to

ws.range("C3").Copy worksheets("Master").cells(CellRef-1,1)

[ghan7650]

Hi Arlu,

I am getting a santax error on the code that you provided..

[arlu1201]

Can you please provide the rest of the code?

[ghan7650]

Hi,

Please see attached. Below are the codes. they can also be found in the macro edit function.

Sub Master()

    Dim ws As Worksheet
    Dim CellRef As Long
    Dim EmpName As String


CellRef = 4

   
    For Each ws In ThisWorkbook.worksheets
    
           If ws.Name <> "Master" Then
      
            
                ws.PivotTables("PivotTable1").TableRange2.Copy Destination:=worksheets("Master").Cells(CellRef, 1)
    
           
                
                EmpName = ws.range("C3").Copy worksheets("Master").cells(CellRef-1,1)
                
                CellRef = CellRef + ws.PivotTables("PivotTable1").TableRange2.Rows.Count + 5
                
                
        
        End If
    
    Next ws


End Sub

[arlu1201]

This is your updated code

Sub Master()

Dim ws As Worksheet
Dim CellRef As Long
    
CellRef = 4

    'runs a loop for the same number of times as the number of worksheets in the active workbook
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Master" Then
        'copies the pivot table from various tabs to the master tab in cell (CellRef, 1)
         ws.PivotTables("PivotTable1").TableRange2.Copy Destination:=Worksheets("Master").Cells(CellRef, 1)
         Worksheets("Master").Cells(CellRef - 1, 1).Value = ws.Cells(3, 3).Value
         CellRef = CellRef + ws.PivotTables("PivotTable1").TableRange2.Rows.Count + 5
    End If
Next ws

End Sub

[ghan7650]

Works Cheers!!