CODEWITHSUNDEEP
excelvba
Sharma S
Sharma S

Reputation: 1

To save single worksheet in Excel to source folder with desired name

I have a macro enabled workbook in my local folder. This workbook consists of 7 worksheet in total. Last sheet named as "AnsSheet". I want to save the last sheet (AnsSheet only) in the same folder location with modified name. Here is the code I am using which is not giving desired result.

Could you please guide?

Sheets("AnsSheet").Select
Set wb = Workbooks.Add
ThisWorkbook.Sheets("AnsSheet").Copy Before:=wb.Sheets(1)
ActiveSheet.SaveAs Filename:=ActiveWorkbook.Path & "\WF_Macro_" & Format(Date, "DD-MMM-YYYY") & ".xls"

Upvotes: 0

Views: 562

Answers (1)

Socii
Socii

Reputation: 545

Your Filename will be incomplete as ActiveWorkbook.Path will be blank. The ActiveWorkbook will be your newly created Workbook, and as you haven't saved it yet the Path will be empty. Use ThisWorkbook instead to get the path of the current Workbook.

I'm not sure if the ActiveSheet.SaveAs method will work but I haven't looked into it. Personally I would use the Workbook.SaveAs method to save the new Workbook. Also, instead of adding ".xls" to the end of the filename, you should specify the filetype using the FileFormat parameter MSDN FileFormat Enum

I've updated your code below with comments to help see what is going on:

Dim wb As Excel.Workbook

'\\ Create a new Workbook with only one Worksheet
Set wb = Workbooks.Add(xlWBATWorksheet)

'\\ Copy Sheet to start of new Workbook
ThisWorkbook.Sheets("AnsSheet").Copy Before:=wb.Sheets(1)

'\\ Turn off alerts and delete the unused sheet, turn alerts back on
Application.DisplayAlerts = False
wb.Sheets(2).Delete
Application.DisplayAlerts = True

'\\ Save new Workbook as a standard Workbook
wb.SaveAs Filename:=ThisWorkbook.Path & "\WF_Macro_" & Format(Date, "DD-MMM-YYYY"), _
          FileFormat:=xlWorkbookNormal

Upvotes: 1

Related Questions