Pivot Table Data Range Update Windows' title='Pivot Table Data Range Update Windows' />Referencing Pivot Table Ranges in VBAHi Jon,Ive tried to incorporate a Select Case block into my code as you suggested, however, I now get a Run Time Error 1. Source. Data RangeA4. Current. Region. AddressTrue, True, xl. R1. C1, TrueThe error message reads as follows The Pivot Table field name is not valid. To create a Pivot. Table report, you must use data that is organised as a list with labelled columns. Pivot Table Data Range Update Windows' title='Pivot Table Data Range Update Windows' />O fyou are changing the name of a Pivot. Table field, you must type a new name for the field. My original code worked ok prior to the selct case block so Im assuming it may have something to do with that Please see below my new code with the Select Case block Sub RefreshAllPivotsOn Error Go. To errhandler. Application. Screen. Updating False. Dim field. 1 As String. Dim field. 2 As String. Dim field. 3 As String. Dim field. 4 As String. Dim field. 5 As String. Dim field. 6 As String. Pivot Table Data Range Update Windows' title='Pivot Table Data Range Update Windows' />Dim field. As String. Dim field. As String. I have a pivot table that gets updated like this Sub Refresh Refresh Macro ActiveSheet. PivotTablesPivotTable1. PivotCache. Refresh WorksheetChangeByVal. Dim ws As Worksheet. Dim pt As Pivot. Table. Dim pf As Pivot. Field. Dim rng As Range. Dim lastcol As Long. Dim Last. Row As LongActive. Sheet. Pivot. TablesPvt. Variance. Rpt. 2. Installed Flash Player there. Pivot. FieldsLine. Current. Page StrFor Each ws In Active. Workbook. Worksheetsws. Activate. For Each pt In ws. The question is how do we bridge the gap between the user friendly source table and the pivot friendly normalized table above Below I describe two vbabased techniques. Pivot. Tables Select Case pt. Name Case Pivot. Table. Pivot. Table. 28, Pivot. Table. 29 Set rng RangeA4. Current. Region Last. Row Last1, rnglastcol Last2, rng field. Cells1, lastcol 7. Textfield. 2 rng. Cells1, lastcol 6. Textfield. 3 rng. Cells1, lastcol 5. Textfield. 4 rng. Cells1, lastcol 4. Textfield. 5 rng. Cells1, lastcol 3. Textfield. 6 rng. Cells1, lastcol 2. Textfield. 7 rng. Cells1, lastcol 1. Textfield. 8 rng. Cells1, lastcol. Text pt. Source. Data RangeA4. Current. Region. AddressTrue, True, xl. R1. C1, True pt. Data. Pivot. Field. Orientation xl. Hidden With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With Case Pivot. Table. 30 Set rng RangeF4. Current. Region Last. Row Last1, rnglastcol Last2, rng field. Cells1, lastcol 7. Text not sure if the. Cells1, lastcol 6. Text not sure if the. Cells1, lastcol 5. Textfield. 4 rng. Cells1, lastcol 4. Textfield. 5 rng. Cells1, lastcol 3. Textfield. 6 rng. Cells1, lastcol 2. Textfield. 7 rng. Cells1, lastcol 1. Textfield. 8 rng. Download Amor A Distancia Ulrich Beck Pdf Descargar on this page. Cells1, lastcol. Text pt. Source. Data RangeF4. Current. Region. AddressTrue, True, xl. R1. C1, True pt. Data. Pivot. Field. Orientation xl. Hidden With pt. Pivot. Fieldsfield. End With With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With Case Pivot. Table. 33 Set rng RangeF5. Current. Region Last. Row Last1, rnglastcol Last2, rng field. Cells1, lastcol 7. Text not sure if the. Cells1, lastcol 6. Text not sure if the. Cells1, lastcol 5. Textfield. 4 rng. Cells1, lastcol 4. Textfield. 5 rng. Cells1, lastcol 3. Text pt. Source. Data RangeF5. Current. Region. AddressTrue, True, xl. R1. C1, True pt. Data. Pivot. Field. Orientation xl. Hidden With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With With pt. Pivot. Fieldsfield. End With With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With With pt. Pivot. Fieldsfield. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With With pt. Pivot. Fieldsfield. Orientation xl. Data. Field. Caption Sum of field. Function xl. Sum. End With Case Else Msg. Box Unrecognised Pivot table. Cr. Lf vb. Cr. Lf Please check the workbook., vb. OKOnly, Error with Refreshing Pivots End Selectpt. Refresh. Table. Errorhandling Next pt. Next ws. Active. Workbook. Show. Pivot. Table. Field. List False. Application. Screen. Updating True. Sheet. Activate. Exit Suberrhandler Msg. Box One or more of the Pivot tables have experienced an error. Cr. Lf vb. Cr. Lf Please look through the workbook to determine where the error has occurred., vb. OKOnly, Error with Refreshing PivotsResume Errorhandling. End Sub. Do you have any ideas why the Select Else block would provide an error Thanks in advance.