CODEWITHSUNDEEP
vbaexcel
Jenny
Jenny

Reputation: 451

Error while saving a particular sheet in Folder

I have an sheet BU. In the sheet BU I have 4 button. Among the 4 button, I have an button called as Save.

With this save button, I am saving the sheet BU in a particular folder.

BU is an Xlsm file, and I want the same sheet BU to be saved as xlsx.

I have the below code. But everytime I am trying to implement I get an error called

The following macro cant be saved in macro free workbook.

Could anyone help me to overcome this problem ?

I am using the program below, I tried saving in xlsm as well, it dint work 

Sub save()
Dim myWorksheets() As String
Dim newWB As Workbook
Dim CurrWB As Workbook
Dim i As Integer
Dim path1, Path2 As String
path1 = ThisWorkbook.Path
Path2 = path1 & "\Arc\Mat\"
Set CurrWB = ThisWorkbook
myWorksheets = Split("BU", ",")
For i = LBound(myWorksheets) To UBound(myWorksheets)
Set newWB = Workbooks.Add
CurrWB.Sheets(Trim(myWorksheets(i))).Copy Before:=newWB.Sheets(1)
newWB.SaveAs filename:=Path2 & Format(Now(), "ww") & myWorksheets(i) & ".xls"
newWB.Close SaveChanges:=False
Next i
Application.ScreenUpdating = False
MsgBox ("File Saved")
 End Sub

Upvotes: 0

Views: 48

Answers (1)

jkpieterse
jkpieterse

Reputation: 3006

Like so:

Sub save()
    Dim newWB As Workbook
    Dim CurrWB As Workbook
    Dim i As Integer
    Dim path1, Path2 As String
    path1 = ThisWorkbook.Path
    Path2 = path1 & "\Arc\Mat\"
    Set CurrWB = ThisWorkbook
    myWorksheets = Split("BU", ",")
    For i = LBound(myWorksheets) To UBound(myWorksheets)
        Set newWB = Workbooks.Add
        CurrWB.Sheets(Trim(myWorksheets(i))).Copy Before:=newWB.Sheets(1)
        'Prevent message about VBA:
        Application.DisplayAlerts = False
        newWB.SaveAs Filename:=Path2 & Format(Now(), "ww") & myWorksheets(i) & ".xlsx", FileFormat:=xlOpenXMLWorkbook
        Application.DisplayAlerts = True
        newWB.Close SaveChanges:=False
    Next i
    Application.ScreenUpdating = False
    MsgBox ("File Saved")
End Sub

If it is always the BU sheet, this can be shortened to:

ThisWorkbook.Worksheets("BU").Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Arc\Mat\" & Format(Now(), "ww") & " BU.xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=False

Upvotes: 1

Related Questions