update Excel File Name (Rename an Excel Sheet), and it auto updates a specific cell in the workbook

Budman361530 You say "but it is not working" but don't say why, how, what line it breaks on ...

That said I see that you are getting strName but this part of that statement doesn't make sense:

InStrRev(ActiveWorkbook.Name, ".", -1,"JOB PO List")

That last part "JOB PO List" looks like a sheet name or something but that should be a parameter defining the type of InStrRev to be done.

also I don't see that you are even using strName in the save as.

so copied the parts and merged together and made comments inside.  I think that should work:

Sub sb_Copy_Save_Worksheet_As_Workbook()

Dim wb As Workbook
Set wb = Workbooks.Add
          'If you want the same "path building as the pdf version" then copy this part from that pdf macro:

Dim strPath As String
Dim strName As String
Dim strFile As String
Dim strPathFile As String
strPath = ThisWorkbook.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "/Contract Quote "

'Getting the file name dynamically
strName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))

'Creating the new file location and name
strFile = strName & ".xlsm"      'NOTE I change the file extension here
strPathFile = strPath & strFile

       'and then add the code you specifically want for this new workbook 
ThisWorkbook.Sheets.Copy Before:=wb.Sheets(1)
wb.SaveAs strPathFile, xlOpenXMLWorkbookMacroEnabled

End Sub

mtarler That did the trick for saving in a Macro.  Thank you.  What am I doing wrong with trying to get the code to save dynamically, both Name and Location?

I have a code that I made that saves pages in a PDF, to whatever folder the WB is in at that moment, and pulls the name from the name of the workbook it self.  example.... if the WB was located in folder X, and was named  20MJNXXX. Customer 1. Job 1. REV. 1,  The PDF would be called Contract Quote. 20MJNXXX. Customer 1. Job 1. REV. 1, and save automatically in folder X. 

Here is the code...

Sub MacroContractQuote()

'
' Macro1 Macro
'
'
'ChDir _
'"Z:\Shared\Budman Shared\Budman Quote and Jobs\01 - Master Quote Folder Templet REV. 1"
'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
'"Z:\Shared\Budman Shared\Budman Quote and Jobs\01 - Master Quote Folder Templet REV. 1" _
', Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
':=False, OpenAfterPublish:=False
Dim strPath As String
Dim strName As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
strPath = ThisWorkbook.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "/Contract Quote "

'Getting the file name dynamically
strName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))

'Creating the new file location and name
strFile = strName & ".pdf"
strPathFile = strPath & strFile
'export to PDF in current folder
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPathFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile
'MsgBox ("New file: " & strPathFile)
End Sub

I am trying to get the same type of results from the excel saving macro, but it's not working...

Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim wb As Workbook
Set wb = Workbooks.Add
'Getting the file name dynamically
strName = (Left(ActiveWorkbook.Name,(InStrRev(ActiveWorkbook.Name, ".", -1,"JOB PO List") - 1))
ThisWorkbook.Sheets.Copy Before:=wb.Sheets(1)
wb.SaveAs strPath 'current folder', xlOpenXMLWorkbookMacroEnabled
End Sub

Budman361530 The SaveAs command is trying to save as the same type it is (default is non-macro) so you need to force it to be xlsm (adding the extension on the name is not enough). Add xlOpenXMLWorkbookMacroEnabled to your SaveAs line like this:

wb.SaveAs "C:\Users\Budman361530\Documents\New folder\Tasksheet.xlsm", xlOpenXMLWorkbookMacroEnabled

mtarler See your point.... I was trying to keep it simple, and I was making things more complicated.  Here is the the coding of my macro.   First I am trying to just get it to work, and I can't.  Next I was trying to get it to preform like another Macro I have, that works for saving different sheets in a PDF in whatever folder they are in, with whatever name the file is.   

First Try.... wont save as a Macro enabled WB. In "debug" it show the wb.saveas line as the issue.

Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Task Sheet.").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\Users\Budman361530\Documents\New folder\Tasksheet.xlsx"
End Sub

Second Try... wont allow me to save as an .xlsm

Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Task Sheet.").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\Users\Budman361530\Documents\New folder\Tasksheet.xlsm"
End Sub

Ulitmaly tyring to work as this code...., but saving as a Macro WB vs. a PDF.

Sub MacroContractQuote()

'
' Macro1 Macro
'
'
'ChDir _
'"Z:\Shared\Budman361530 Shared\Budman361530 Quote and Jobs\01 - Master Quote Folder Templet REV 1.1 DO NOT USE WORKING ON THIS"
'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
'"Z:\Shared\Budman361530 Shared\Budman361530 Quote and Jobs\01 - Master Quote Folder Templet REV 1.1 DO NOT USE WORKING ON THIS\20MJNXXXX REV 1.1 - Copy.pdf" _
', Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
':=False, OpenAfterPublish:=False
Dim strPath As String
Dim strName As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
strPath = ThisWorkbook.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "/Contract Quote "

'Getting the file name dynamically
strName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))

'Creating the new file location and name
strFile = strName & ".pdf"
strPathFile = strPath & strFile
'export to PDF in current folder
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPathFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile
'MsgBox ("New file: " & strPathFile)
End Sub

Budman361530  Without more info on the sheets and file structure I'm in the dark.  If you tried before and couldn't get the macro to work, why didn't it work?  Why not post that sheet or at least the macro to this forum so someone can help you fix it and make it work.  Make sure to give all the information because it is hard for us to guess what you mean or want or intended.

mtarler Yes you have the idea, but I do about 50 a month.  Each folder has multilabel folders, word docs, and an estimating program that has nine sheets.  Each sheet populates with information, and I use macro's to do a lot of the functions.   One of the nine sheets I need my Forman to have access to, but I don't want them involved with the rest of the sheets.  So I basically have that sheet as a stand alone sheet in each folder.   I tried to have that one sheet print or save as a stand alone Work Sheet/book, with a macro, but I couldn't get it to work.