Save specific Sheet from current Macro Enabled workbook to a new file that is not macro enabled.

[GAJITCS]

Hello and thank you for looking.

I have a workbook with multiple sheets. The sheet I would like to save by itself is named "Export To Excel".

Cell AK3 on this sheet contains text that I would like to use as the SaveAs filename suffixed with the current time and date. i.e "CompanyX 2018-01-30 16:35:55".

The file needs to be saved as a .xlsx file and therefore not macro enabled.

Can anyone help with the VBA code please to accomplish this and in doing so, also get around the prompt that informs that this was a macro enabled file and now won't be.

Many thanks,

G.

 

[Fluff]

How about

Sub SaveSht()
   Dim Pth As String
   
   Pth = ThisWorkbook.Path
   Sheets("Export To Excel").Copy
   ActiveWorkbook.SaveAs Pth & "\" & Range("AK3").Value & " " & Format(Now, "yyyy-dd-mm hh_mm_ss") & ".xlsx", 51
   ActiveWorkbook.Close False
End Sub

 

[GAJITCS]

How about

Sub SaveSht()
   Dim Pth As String
   
   Pth = ThisWorkbook.Path
   Sheets("Export To Excel").Copy
   ActiveWorkbook.SaveAs Pth & "\" & Range("AK3").Value & " " & Format(Now, "yyyy-dd-mm hh_mm_ss") & ".xlsx", 51
   ActiveWorkbook.Close False
End Sub

Thank you.

I also added

Application.DisplayAlerts = False

and

Application.DisplayAlerts = True

In order to avoid being plagued by screen prompts.

 

[Fluff]

Glad to help & thanks for the feedback