3

In the below code it saves the complete workbook. I want to save only the active worksheet.

Sub sbVBS_To_SAVE_ActiveWorkbook()
ActiveWorkbook.Save
End Sub
  • You can export the active sheet to a new workbook and save it there but you cannot save one worksheet out of several in a single workbook. –  Feb 23 '18 at 05:21
  • Thank you for your replying @Jeeped –  Feb 23 '18 at 05:23
  • So I have to save all the sheets in different workbook. –  Feb 23 '18 at 05:24
  • yes, it is easy to create a new workbook with a copy of one of the worksheets from the current workbook. –  Feb 23 '18 at 05:33
  • `Sub SaveAsMacro() Dim strDir As String 'Show standard save as dialogue box With Application.FileDialog(msoFileDialogSaveAs) If .Show = -1 Then strDir = .SelectedItems(1) End If End With Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=strDir Application.DisplayAlerts = True End Sub` –  Feb 23 '18 at 05:34
  • This code is also saving the complete workbook. It's just making a duplicate one –  Feb 23 '18 at 05:35

1 Answers1

8

Copying a worksheet to no location automatically creates a new workbook in the foreground with a copy of the worksheet as the only worksheet in the new workbook.

Sub test()
    worksheets("sheet3").copy
    'there is now a new active workbook
    with activeworkbook
        'save it
        .SaveAs Filename:="some file path and filename without extension", FileFormat:=xlOpenXMLWorkbook
        'optionally close it
        .close savechanges:=false
    end with
End Sub
  • `Sub test() Worksheets("Sheet1").Copy With ActiveWorkbook .SaveAs Filename:="C:\Users\my_name\Documents\Excel\test", FileFormat:=xlOpenXMLWorkbook .Close savechanges:=False End Sub ` –  Feb 23 '18 at 06:11
  • You are missing the End With I had in the code above. –  Feb 23 '18 at 06:13
  • i have added `End with` now and it's showing expected end sub –  Feb 23 '18 at 06:16
  • great job. Thanks for the help –  Feb 23 '18 at 06:21