Using VBA to save and rename spreadsheet

[rachel06]

Hello,

Hoping this is a fairly easy question. My team currently uses the below to save and rename a file. I'm wanting us to have a way to add our initials to the front of the file name. I've added a spot for it in cell G7 on a sheet called "Analysis". Is there an easy way to fit that into the below?

Sub iPrepApprFile()
'
' Prepare Approval File
' Delete unneeded tabs
' Save as Approval File without macros
'
Dim WBName, WBPath, BaseName, NewName, EnterName As String
Dim NBeg, NEnd, Response As Integer


' Extract Job ID from Builder name
WBName = ActiveWorkbook.Name
WBPath = ActiveWorkbook.Path
NBeg = InStr(1, WBName, "UMR")
NEnd = InStr(NBeg, WBName, " ")
BaseName = Mid(WBName, NBeg, NEnd - NBeg)

' Create new file name - "[JobID] Approval File.xlsx"
NewName = BaseName & " Approval File"

' Save as new file name, or allow user to enter name
Response = MsgBox("Save as " & NewName & "?", 3, NewName)

' 6 = Yes, 7 = No, Else is Cancel
Select Case Response
' If 6, exit Select and save as new file name
Case 6
' Get file name from user
Case 7
NewName = InputBox("Name to save as? ", "Save As")
' If Cancel, exit Sub
If NewName = "" Then Exit Sub
Case Else
Exit Sub
End Select


' Save with new file name
NewName = WBPath & "\" & NewName
ActiveWorkbook.SaveAs Filename:= _
NewName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


End Sub

TIA!
Rachel

 

[davesexcel]

possible

NewName = WBPath & "\" & sheets("Analysis").range("G7").value & "-" & NewName

 

[rachel06]

That one is giving me problems unfortunately.
What if I tried declaring the analyst initials as a variable? Adding something like this at the beginning (where AnIn is Analyst initials):

Dim AnIn As Range

How would I fit that into the rest of the code? Or better yet, how do I tell this what cell AnIn is? I think I know how to fit it in the rest of the way after that.

Sub iPrepApprFile()
'
' Prepare Approval File
' Delete unneeded tabs
' Save as Approval File without macros
'
Dim WBName, WBPath, BaseName, NewName, EnterName As String
Dim NBeg, NEnd, Response As Integer
Dim AnIn As Range



' Extract Job ID from Builder name
WBName = ActiveWorkbook.Name
WBPath = ActiveWorkbook.Path
NBeg = InStr(1, WBName, "UMR")
NEnd = InStr(NBeg, WBName, " ")
BaseName = Mid(WBName, NBeg, NEnd - NBeg)

' Create new file name - "[JobID] Approval File.xlsx"
NewName = BaseName & " Approval File"

' Save as new file name, or allow user to enter name
Response = MsgBox("Save as " & NewName & "?", 3, NewName)




' 6 = Yes, 7 = No, Else is Cancel
Select Case Response
' If 6, exit Select and save as new file name
Case 6
' Get file name from user
Case 7
NewName = InputBox("Name to save as? ", "Save As")
' If Cancel, exit Sub
If NewName = "" Then Exit Sub
Case Else
Exit Sub
End Select


' Save with new file name
NewName = WBPath & "\" & "-" & NewName
ActiveWorkbook.SaveAs Filename:= _
NewName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


End Sub

 

[davesexcel]

What are the problems you are getting?

 

[rachel06]

It didn't append anything to the front of the file name.