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
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