View original (French)

[VBA/EXCEL] :Save only one sheet?

Solved
Pemex Posted messages 8 Status Member -  
Pemex Posted messages 8 Status Member -
Hello,

How can I save an Excel sheet, but not the entire Workbook?

If I enter:

ActiveSheet.SaveAs Filename:=File_Name
ActiveWorkbook.ActiveSheet Filename:=File_Name

It saves the entire Workbook...

And the formula

Sheets("NAME").SaveAs

doesn't work either.

Does anyone know the answer? Thanks in advance.
Configuration: Windows XP Internet Explorer 6.0

1 answer

Answer 1 / 1
Polux31 Posted messages 7219 Status Member 1 204
 
Hello,

You need to create a new workbook with a single sheet and then copy the sheet into the new workbook :: 
Dim wk As Workbook Dim ws As Worksheet Set wk = Workbooks.Add(xlWBATWorksheet) Set ws = ThisWorkbook.Worksheets(3) ws.Copy After:=wk.Sheets(Sheets.Count)


;o)
--
"What is well conceived is clearly stated, and the words to say it come easily."
Nicolas Boileau
8
Pemex Posted messages 8 Status Member
 
Hello,

Great, it works very well.

Attached is the code to save the new sheet (by inserting the report date into the name) and to close it: 

 Private Sub CommandButton1_Click() 'copy save report Dim wk As Workbook Dim ws As Worksheet Set wk = Workbooks.Add(xlWBATWorksheet) Set ws = ThisWorkbook.Worksheets("Cond_Ope_Fuerzas") ws.Copy After:=wk.Sheets(Sheets.Count) Dim name, path As String name = Day(Date) & "-" & Month(Date) & "-" & Year(Date) & "_" & ActiveSheet.Name path = Workbooks("Prog_Extraccion_datos").Path & "\" & name ActiveWorkbook.ActiveSheet.SaveAs Filename:=path rep = MsgBox("The Forces report has been saved to: " & name, vbYes + vbInformation, "Save the report") ActiveWorkbook.Close End Sub


Thank you,

++
0