Vba to save sheet as new workbook without Formulas or macros

[twl2009]

Is it possible to save sheet as seperate workbook, but only values no formulas and no macros or code. Oh and also I would really like it save from row 2, so leaving row 1 out of the copy.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff; min-height: 13.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #008f00; background-color: #ffffff}p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}span.s1 {color: #011993}span.s2 {color: #000000}span.s3 {color: #008f00}</style>
Sub Button4()
[COLOR=#000000]
[/COLOR]



ActiveSheet[COLOR=#008F00].SaveAs Filename:="/Users/User/Desktop/" & "Expenses " & Format(Range("E1"), "Mmm yy")

[/COLOR]
[COLOR=#011993]End[/COLOR] [COLOR=#011993]Sub

[/COLOR][FONT=Verdana]

[/FONT]

 

[gallen]

Firstly you can't have Forward slashes in the name of a file.

I think this code does what you ask. Please test on copy of data:

Please ensure you change the variables to suit what you are trying to achieve

Sub SaveValuesOnly()
Dim wsCopy As Worksheet, wsPaste As Worksheet
Dim wb As Workbook
Dim sFileName As String, sPath As String


    'Path to store new file
    sPath = "C:\Test\"
    'Change filename as required
    sFileName = "Expenses " & Format(Range("E1"), "Mmm yy")
    
    'set the sheet you are copying. Change where neccessary
    Set wsCopy = ThisWorkbook.Worksheets("Sheet1")
    Set wb = Workbooks.Add
    Set wsPaste = wb.Sheets(1)
    
    'Copy everything from copy sheet
    wsCopy.Cells.Copy
    'Paste Values only
    wsPaste.Cells.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    'delete first row
    wsPaste.Rows(1).Delete
    
    'Save new workbook
    
    wsPaste.Name = "Expenses" 'Change if needed
    wb.SaveAs Filename:=sPath & sFileName, FileFormat:=xlOpenXMLWorkbook
    
End Sub

 

[twl2009]

Thats brilliant thanks gallen, is it possible to keep formatting, font, and shading.

 

[twl2009]

Ignore that, I have found it, nice and simple .... xlPasteFormats

 

[wingmarshall]

Sub SaveValuesOnly()
Dim wsCopy As Worksheet, wsPaste As Worksheet
Dim wb As Workbook
Dim sFileName As String, sPath As String


'Path to store new file
sPath = "C:\Scripts"
'Change filename as required
sFileName = "Server " & Format(Range("E1"), "Mmm yy")

'set the sheet you are copying. Change where neccessary
Set wsCopy = ThisWorkbook.Worksheets("Sheet3")
Set wb = Workbooks.Add
Set wsPaste = wb.Sheets(1)

'Copy everything from copy sheet
wsCopy.Cells.Copy
'Paste Values only
wsPaste.Cells.PasteSpecial xlPasteValues
Application.CutCopyMode = False

'delete first row
'wsPaste.Rows(1).Delete

'Save new workbook

wsPaste.Name = "Expenses" 'Change if needed
wb.SaveAs Filename:=sPath & sFileName, FileFormat:=xlOpenXMLWorkbook

End Sub

Why I am getting error when i try to copy sheet3 please advice

 

[Alessio9899]

Firstly you can't have Forward slashes in the name of a file.

I think this code does what you ask. Please test on copy of data:

Please ensure you change the variables to suit what you are trying to achieve

Sub SaveValuesOnly()
Dim wsCopy As Worksheet, wsPaste As Worksheet
Dim wb As Workbook
Dim sFileName As String, sPath As String


    'Path to store new file
    sPath = "C:\Test\"
    'Change filename as required
    sFileName = "Expenses " & Format(Range("E1"), "Mmm yy")
   
    'set the sheet you are copying. Change where neccessary
    Set wsCopy = ThisWorkbook.Worksheets("Sheet1")
    Set wb = Workbooks.Add
    Set wsPaste = wb.Sheets(1)
   
    'Copy everything from copy sheet
    wsCopy.Cells.Copy
    'Paste Values only
    wsPaste.Cells.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
   
    'delete first row
    wsPaste.Rows(1).Delete
   
    'Save new workbook
   
    wsPaste.Name = "Expenses" 'Change if needed
    wb.SaveAs Filename:=sPath & sFileName, FileFormat:=xlOpenXMLWorkbook
   
End Sub

thanks for your excellent code! But what if i need some specific sheets instead of just one?
I mean for example ThisWorkbook.Worksheets("Sheet1","Sheet5","Sheet9")
I tried with array function but couldn't implement inside your code, can you help me?