PDA

View Full Version : VBA Work Sheet As Work Book Run Time Error



bloodmilksky
10-27-2016, 05:37 AM
Hi Guys

i was just wondering if anyone can help me with the below. Would anyone know how I can save this as a VB Free file and change the sheet that I am saving to Values and not formulas but keep the original sheet intact?

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("Menu").Range("E9").Value
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Menu").Copy Before:=wb.Sheets(2)
wb.SaveAs myPath & myFilename & myFileExtension
wb.Close False
Msg = "Would You Like To Place Another Order ? " & Application.UserName
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then
Range("ADDRESSREF").Select
Selection.ClearContents
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.Quit
End If
If Ans = vbYes Then
Sheets("Menu").Select
Range("E6").Select
Selection.ClearContents
Run "NextOrder"

End If

End Sub

p45cal
10-27-2016, 09:38 AM
completely untested:
After:
ThisWorkbook.Sheets("Menu").Copy Before:=wb.Sheets(2)
add the line:
Activesheet.usedrange.value = activesheet.usedrange.value

bloodmilksky
10-28-2016, 05:40 AM
Thats great thank you

bloodmilksky
10-28-2016, 08:23 AM
could I quickly ask you another question relating to the same code? please

p45cal
10-28-2016, 09:54 AM
yes

bloodmilksky
10-31-2016, 04:52 AM
my query is that when I do run it and it goes to save the sheet as a seperate workbook it comes up with

"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 and working that into the code as Ideally i would like it to run seamlessley.

Thank you for your time

Many thanks

Jamie

p45cal
10-31-2016, 10:23 AM
I'm not near a computer just now but you could try putting:
Application.displayalerts=False
just before the wb.saveas line and
Application.displayalerts =true
straight after it.

bloodmilksky
11-02-2016, 02:39 AM
Thank You P45cal ^_^