Removing the [Link reference] from a copied formula

[sdoppke]

Hi everyone I have learned a lot but still not working yet. I am copying worksheet from one workbook to another, but want only the formula not the [lined reference]. i though this would do, but it doesnt, would really appreciate any help


this is the full macro:

Sub GetQuotaSheet()
'
' GetQuotaSheet Macro
    Dim wb As Workbook
    Application.ScreenUpdating = False
    Sheets("VCR Copy and Paste").Visible = True
    Workbooks.Open Filename:= _
        "S:\WASeattle\WFM\Test\Sales Contribution Calculator and Ranker-June.xlsm"
    Sheets("June Quotas").Select
    Cells.Select
    Selection.Copy
    Windows("WFMToolbackupLiteDateImport3.xlsm").Activate
    Sheets("Quotas").Visible = True
    Sheets("Quotas").Select
    Range("A1").Select
    ActiveSheet.Paste
    
    Dim cell As Range, n As Variant
    For Each cell In Workbooks("WFMToolbackupLiteDateImport3.xlsm").Sheets("Quotas").Cells.SpecialCells(xlFormulas)
        n = Application.Find("]", cell.Formula)
        If Not IsError(n) Then
            cell.Formula = "='" & Right(cell.Formula, Len(cell.Formula) - n)
        End If
    Next cell
    Range("A1").Select
    Selection.ClearContents
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=MyStoreInfo!R[1]C"
    Range("C1").Select
    Columns("C:C").EntireColumn.AutoFit
    Range("C1").Select
    For Each WkbkName In Application.Workbooks()
            If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close WkbkName.Saved = False
    Next
    Sheets("VCR Copy and Paste").Select
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Quotas").Select
    Range("AF26").Select
    ActiveSheet.Paste
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A15").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$AF$26:$AF$226"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Range("C1").Select
End Sub

This is the part that should do the trick, but doesnt

Dim cell As Range, n As Variant
    For Each cell In Workbooks("WFMToolbackupLiteDateImport3.xlsm").Sheets("Quotas").Cells.SpecialCells(xlFormulas)
        n = Application.Find("]", cell.Formula)
        If Not IsError(n) Then
            cell.Formula = "='" & Right(cell.Formula, Len(cell.Formula) - n)
        End If
    Next cell

 

[sdoppke]

Does anyone have any ideas on this? would it help if I reworded the question at all?

Thanks in advance for any help with this!

sd