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

Brass Contributor

I do have a question, that I seem to be hitting a wall on.  Is it possible to update an Excel File Name (Rename an Excel Sheet), and it auto update a specific cell in the workbook?  Here is the next part of the question.  I need this program to be able to follow the file, regardless where it is saved.  Meaning, if update the file on my computer, but email it to someone, I want the formula dynamic.  I need it to follow the sheet NOT look for coding to “My documents”, or “My computer” to know what the cell name is.  And if someone changes the file name on their computer, after I sent it to them, it auto updates the cell to the new file name they name it.

20 Replies

@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