WorkSheet To WorkBook cant be saved error

[bloodmilksky]

Hi Guys,

I am using the below code to save a sheet as a workbook. my query is that when I do run it a notification comes up saying:

"parts of this workbook you are trying to save can not be saved macro free : VB Project"

I just wondered if there is a way of saving this as a macro free workbook

Many thanks

Jamie

Sub Copy_Save_Worksheet_As_Workbook()


    Dim wb As Workbook
    Dim myPath As String
    Dim myFilename As String
    Dim myFileExtension As String
    myPath = "D:\" 'you can change this
    myFileExtension = ".xlsx"
    myFilename = ThisWorkbook.Sheets("EDI template").Range("REFNUMBER").Value
    Set wb = Workbooks.Add
    ThisWorkbook.Sheets("EDI template").Copy Before:=wb.Sheets(1)
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    wb.SaveAs myPath & myFilename & myFileExtension
    wb.Close False
    Msg = "Would You Like To Load Another Order ? " & Application.UserName
    Ans = MsgBox(Msg, vbYesNo)
    If Ans = vbNo Then
                Range("Description").Select
                Selection.ClearContents
                Range("PXINFOR").Select
                Selection.ClearContents
                Application.DisplayAlerts = False
                ThisWorkbook.Save
                Application.DisplayAlerts = True
                Application.Quit
                End If
    If Ans = vbYes Then
                Range("PXINFOR").Select
                Selection.ClearContents
                Range("Description").Select
                Selection.ClearContents
                Run "NextOrder"
    
                End If
                
End Sub

 

[Joe4]

I just wondered if there is a way of saving this as a macro free workbook

One way would be to have your VBA code copy the sheet to a new, blank workbook and save that.

 

[steve the fish]

Heres a snippet of the code I would use to do that.

Application.DisplayAlerts = False
Sheets("Sheet1").Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = True

This will create a new workbook with just Sheet1 in it and assign that workbook to the variable wb

 

[bloodmilksky]

Hi,

Where would I add that to my current code? sorry still learning and stuff and thank you both

 

[bloodmilksky]

it is now also erroring on the wb.SaveAs myPath & myFilename & myFileExtension part as well very weird

 

[steve the fish]

Display the code you are using now. Just to say its erroring is not sufficient. What is the error code? What are the value of the variables at the time of error?

 

[Norie]

Cross post.

 

[Joe4]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.