VBA to name the copied worksheet with designated name and name on worksheet tab

[Bevg]

Hi,

I'm running into a wall. I created vba code that will copy a sheet from my main workbook, create a new workbook and now I need to save it using the name I provide plus the name of the worksheet (which is the date). So the name of the file would look like My new workbook 3.8.19.xls

Any help would be greatly appreciated! Thank you Bev

I'm getting an error at the save as step
Sub copyworksheet2newworkbook()

Dim FName As String
Dim ShtName As String
Dim FPath As String
Dim NewBook As Workbook
Dim TDate As String

FPath = "C:\Users\v-begrov\Desktop\"
ShtName = ActiveWorkbook.Name
FName = "My New file name" & TDate.xls"


Range("Cela_Export").Copy
Set NewBook = Workbooks.Add
Sheets.Add.Name = Format(Date, "mmm-dd-yy")

With NewBook.Sheets(1).Range("A2:O100")
ActiveSheet.Paste
Cells.Select
Range("A2").Activate
Cells.EntireColumn.AutoFit
Cells.Select
ActiveWindow.Zoom = 75
.SaveAs Filename:=FPath & ShtName
.Close



End With

[TMS]

So what doesn't the code you have do that it needs to do ... what is your issue?


Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.