Get Rid Of VBA So I Can Save A New Workbook As .xlsx

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,616
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I copied a worksheet from an .xlsm workbook to a new workbook. The worksheet I copied has worksheet code associated to it. This code was copied over to the new workbook with the sheet.
How do I get rid of this code so I can save the new workbook as an .xlsx file. If I try to save the file as it is, a message pops up to advise the problem trying to save as an xlxx file and request confirmation to save outside an xlsm file.
I can press OK to save as an xlsx, but I'd prefer to have that done automatically.

Code:
Sub saveme()
    Stop
    Application.EnableEvents = False
    Dim wb_diamonds As Workbook
    Dim ws_diamonds As Worksheet
    LastSheet = Sheets.Count
    Worksheets("Front").Copy After:=Sheets(LastSheet)
    ActiveSheet.Name = "COPY ME"
    Worksheets("COPY ME").Unprotect
    With Worksheets("COPY ME").Rows("1:6")
        For Each shp In .Parent.Shapes
            If Not Intersect(shp.TopLeftCell, .Cells) Is Nothing Then shp.Delete
        Next shp
        Stop
        .Delete
    End With
    Worksheets("COPY ME").Columns("A:I").EntireColumn.Delete
    Worksheets("COPY ME").Protect
    Worksheets("COPY ME").Move

    ActiveWindow.FreezePanes = False
    fdate = Worksheets("COPY ME").Range("A5")
    mnth = MonthName(Month(fdate), True)
    day2 = Format(Day(fdate), "00")
    dayt = UCase(Format(fdate, "ddd"))
    sfile = day2 & " " & dayt
    fPath = "D:\WSOP 2020\Distributables\" & mnth & "\" & sfile & "\"
    fName = "Diamonds" & day2
    sName = fPath & fName
    fName = "Diamonds" & day2 & ".xlsx"
    sName = fPath & fName
    ActiveWorkbook.SaveAs sName
    Set wb_diamonds = Workbooks(fName)
    wb_diamonds.Worksheets("COPY ME").Name = "D" & day2
    wb_diamonds.Save
    wb_diamonds.Close
    Application.EnableEvents = True
End Sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Ark I believe all you have to do is high light all of the code and delete. Let's us know if this is what you need.
 
Upvote 0
Try just turning DisplayAlerts off (and then back on)

VBA Code:
    Application.DisplayAlerts = False
    wb.SaveAs Filename:="TestXLSX", FileFormat:=51
    Application.DisplayAlerts = True
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top