Converting Textboxes Link and/or Formula to Values in a Copied Sheet using Excel VBA-CodePudding

I'm copying a sheet to another workbook using this VBA code:

Sub Macro1()
    Dim num As Integer
    Dim tbox As TextFrame2
    
    Sheets("2316 Printing Template (v2018)").Select
    Range("AS9").Select
    
    For i = 1 To 2
        Range("AS9").Value = i
        Sheets("2316 Printing Template (v2018)").Copy After:=Workbooks("Book1.xlsx").Sheets(i)
        With ActiveSheet.UsedRange
            .Value = .Value
        End With
        
        Worksheets("2316 Printing Template (v2018)").Name = Left(Range("AS12").Value, 31)
        Windows("2316 PrinterTemplate (2022).xlsm").Activate
    Next i
    
End Sub

The cells and Textbox values change in the template as the Cell AS9 changes value then I copy the template to another workbook and rename the new sheet. My problem now is the textboxes still are linked to the original workbook & sheet and how do I convert to link to value?

Here is a screenshot of the 2 files. The First one is the template:

And this is the copied sheet:

CodePudding user response：

You could use something like this

Sub RemoveFormulaFromTextBoxes()

Dim s As Worksheet
Set s = ActiveWorkbook.ActiveSheet
s.Activate

Dim tf As TextFrame2
Dim v

For Each t In s.Shapes
    If t.Type = 17 Then
        v = t.TextFrame2.TextRange.Characters.Text
        Set tf = t.TextFrame2
        t.Select
        Selection.Formula = ""
        t.TextFrame2.TextRange.Characters.Text = v
    End If
Next
End Sub