CODEWITHSUNDEEP
vbaexcel-formula
mij nivek
mij nivek

Reputation: 81

What code to use in copying the workbook, removing all macros (including form controls), and pasting to another New Workbook w/o removing formula?

I created a workbook that is automated with macro and I want to copy the whole workbook and paste it into another workbook and remove all macro's (including the form controls) except only the formulas.

My code is working copying the workbook and pasting to another workbook (including the formulas) and removing the macros. But the Form controls were still keep on appearing in the new workbook. What code do I need to add? Please help me. My code is written below:

            Private Sub Label16_Click()
              Unload Me
               Dim NewWkb As Workbook
               Dim xWkb As Workbook
               Dim x As Integer

                Set xWkb = Workbooks("Technical Support Database (Automated by MACRO).xlsb")
                Set NewWkb = Workbooks.Add
                NewWkb.SaveAs "C:\Users\TSJeddah\OneDrive - Delta Marketing Co\JIM FILES\Operation Files\" & "TS Database (No MACRO).xlsx"
                For x = 1 To xWkb.Worksheets.Count - 1
xWkb.Sheets(x).Copy after:=NewWkb.Worksheets(NewWkb.Worksheets.Count)
                Next x 
              End Sub

enter image description here

Upvotes: 0

Views: 149

Answers (2)

Алексей Р
Алексей Р

Reputation: 7627

If you need to remove all form controls from the target workbook, you can do so with the following code:

Sub DeleteFormControlsFromWB(WB As Workbook)
    Dim sh As Shape, ws As Worksheet
    For Each ws In WB.Worksheets
        For Each sh In ws.Shapes
            If sh.Type = msoFormControl Or sh.Type = msoOLEControlObject Then
                Debug.Print "Deleted Form control: " & sh.Name    'debug
                sh.Delete
            End If
        Next
    Next
End Sub

Sub UsageExample()
    DeleteFormControlsFromWB ThisWorkbook
End Sub

Upvotes: 1

mij nivek
mij nivek

Reputation: 81

         Private Sub Label16_Click()
          Unload Me
           Dim NewWkb As Workbook
           Dim xWkb As Workbook
           Dim x As Integer

            Set xWkb = Workbooks("Technical Support Database (Automated by 
            MACRO).xlsb")
            Set NewWkb = Workbooks.Add
            NewWkb.SaveAs "C:\Users\TSJeddah\Desktop\JIM\" & "TS Database (No 
            MACRO).xlsx"
            Call UsageExample
            For x = 1 To xWkb.Worksheets.Count - 1
            xWkb.Sheets(x).Copy 
            after:=NewWkb.Worksheets(NewWkb.Worksheets.Count)
           Next x
            ActiveWorkbook.Close
            Application.ScreenUpdating = True
         End Sub

Upvotes: 0

Related Questions