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

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