Taking formula to text

[Dkline]

I have a formula which I need to be able to copy/paste to a "Save As" file
name.

Column A Column B
R1 Prod LP10
R2 DivOp1 PUA
R3 14
R4 M
R5 1

Formula is =Prod&"-"&DivOpt1&"-"&B3&B4&B5

The formula returns "LP10-PUA-14M1".

This is supposed to be the name for a file. Yet I cannot copy this result
and paste the name in the "Save As" file name. I've tried Text, T, and a few
others.

Any alternatives to solve this problem?
End result as text is supposed to be "LP10-PUA-14M1"

 

[ryguy7272]

Put this in Cell E1:
=B1&"-"&B2&"-"&B3&B4&B5

Then run this macro:
Sub SaveSheet()
'error trap
On Error GoTo Etrap

Dim MyCell
MyCell = ActiveCell.Value

'ask user to save
If MsgBox("Save new workbook as " & CurDir & "\" & MyCell & ".xls?",
vbYesNo) = vbNo Then
Exit Sub
End If

'check value of activecell
If MyCell = "" Then
MsgBox "Please check the Cell Value", vbInformation
Exit Sub
End If

'save activeworkbook as new workbook
ActiveWorkbook.SaveAs Filename:=MyCell & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

Etrap:

Beep
Exit Sub

End Sub

Found macro here:
http://www.mrexcel.com/archive/VBA/19609d.html


Regards,
Ryan---

 

[Dkline]

Thanks for your help. I'm good to go.

Put this in Cell E1:
=B1&"-"&B2&"-"&B3&B4&B5

Then run this macro:
Sub SaveSheet()
'error trap
On Error GoTo Etrap

Dim MyCell
MyCell = ActiveCell.Value

'ask user to save
If MsgBox("Save new workbook as " & CurDir & "\" & MyCell & ".xls?",
vbYesNo) = vbNo Then
Exit Sub
End If

'check value of activecell
If MyCell = "" Then
MsgBox "Please check the Cell Value", vbInformation
Exit Sub
End If

'save activeworkbook as new workbook
ActiveWorkbook.SaveAs Filename:=MyCell & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

Etrap:

Beep
Exit Sub

End Sub

Found macro here:
http://www.mrexcel.com/archive/VBA/19609d.html


Regards,
Ryan---

 

[ryguy7272]

Great! Since that solution solved your problem, please click the 'Yes'
button to let me, and others, know this.

Regards,
Ryan---