Posts by Melody00

Re: Preserving formula reference to pivot table

Yes, I have attached the updated spreadsheet

Re: Preserving formula reference to pivot table

Here is the workbook. The dashboard is tab is where the formulas break when the sheets are recreated. Thanks for the help

Quote from KjBox;790619

The problem certainly lies with the sheet deletion, that is causing the #Ref in the formulas.

Can you attach your workbook, impossible to suggest a solution without seeing the sheet(s) that get deleted and how the pivot table references data on those sheets.

[sw]*[/sw]

Here

I have a macro which deletes worksheets and then recreates based off the report filter in the pivot table. Anytime I do this all references to the formulas used to gather the data especially the GetPivotData is lost and i have to fix all the #REF! errors manually. Is there a way to prevent this from happening? I used this code from contextures.

TIA

Sub CopyPivotPages()
'pivot table tutorial by contextures.com
 'creates sheet with copy of pivot table
 'for each item in specified page field
 'pivot item is selected on inserted page pivot table
 
On Error GoTo errHandler
Application.DisplayAlerts = False




Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strPF As String
Dim strPI As String




strPF = "Rep"
Set ws = Sheets("SalesPivot")
Set pt = ws.PivotTables(1)
Set pf = pt.PivotFields(strPF)




For Each pi In pf.PivotItems
  strPI = Left("PT_" & pi.Name, 31)
  On Error Resume Next
    Sheets(strPI).Delete
  On Error GoTo 0
  ws.Copy After:=Sheets(Sheets.Count)
  With ActiveSheet
    .Name = strPI
    With .PivotTables(1).PivotFields(strPF)
      .PivotItems(pi.Name).Visible = True
      .CurrentPage = pi.Name
    End With
  End With
Next pi




ws.Activate




exitHandler:
  Application.DisplayAlerts = True
  Exit Sub
errHandler:
  MsgBox "Could not create sheets"
  Resume exitHandler




End Sub

Re: Circular Reference Erro

Yes, it was in J2. I will post a copy of the workbook shortly

Re: Circular Reference Erro

Thanks for the reply, the circular cell reference went away when i typed ctrl + shift + enter after typing the formula but now i get #N/A, i cant find any matches at all

Hello, I am trying to extract unique values from one column to another but i end with a circular reference error. I have data formatted as a table and am using the following formula. The data is in column D and I am trying to get the unique values to show up in column J. appreciate any help. I have the formula starting column J2

Filed Against Column J
AA
B
C
AA
S
C

index(Table1[@[Filed Against], match(0,countif($j$2:j2,Table1[@[Filed Against])

TIA

Hello,

I am using this code to scroll to the top of the sheet when switching worksheets but its not working, would appreciate any help.

Scenario

Have a button on Sheet1 which onclick takes me to sheet 2, row 20
when i go back to sheet1 or any other sheet i want sheet 2 to scroll up all the way A1.
I have the code on Sheet2 which is not working

Private Sub Worksheet_Selectionchane(ByVal Target As Range)
    Application.Goto Reference = Range("A1"), Scroll = True