Hi,
This code works for me but one issue. I want to save the file under the
first and last name only.

Code:

Sub SaveFile()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Report").Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & Range("C1").Value
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

C1 = John Smith 12/15/1945

what do I need to change in the code to make it save the file as: John Smith

Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & Range("C1").Value

becomes

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\John Smith.xls"



"ielmrani via OfficeKB.com" wrote:

Hi,
This code works for me but one issue. I want to save the file under the
first and last name only.

Code:

Sub SaveFile()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Report").Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & Range("C1").Value
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

C1 = John Smith 12/15/1945

what do I need to change in the code to make it save the file as: John Smith

Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

--

Dave Peterson

The value of C1 is not always John Smith. it's always this format "First Name
last name, date of birth"

so it could be:

John Smith, 12/18/1954
Adam Bloomberg, 1/15/1984
etc

Thanks Dave

Dave Peterson wrote:
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & Range("C1").Value

becomes

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\John Smith.xls"

Hi,
This code works for me but one issue. I want to save the file under the
[quoted text clipped - 22 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

So you want to pick out everything to the left of the first comma?

Dim myName As String
Dim FirstCommaPos As Long
'....stuff that does the copy
With ActiveSheet.Range("C1")
FirstCommaPos = InStr(1, .Value & ",", ",", vbTextCompare)
myName = Left(.Value, FirstCommaPos - 1)
End With

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myName & ".xls"

"ielmrani via OfficeKB.com" wrote:

The value of C1 is not always John Smith. it's always this format "First Name
last name, date of birth"

so it could be:

John Smith, 12/18/1954
Adam Bloomberg, 1/15/1984
etc

Thanks Dave

Dave Peterson wrote:
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & Range("C1").Value

becomes

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\John Smith.xls"

Hi,
This code works for me but one issue. I want to save the file under the
[quoted text clipped - 22 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

--

Dave Peterson

I put the following but it's working:

Sub SaveFile()


Dim myName As String
Dim FirstCommaPos As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Report").Copy

With ActiveSheet.Range("B1")
FirstCommaPos = InStr(1, .Value & ",", ",", vbTextCompare)
myName = Left(.Value, FirstCommaPos - 1)

End With

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myName & ".xls"

End Sub

I get this message: run-time error 1004 in Excel

one I bypass the error it create a new workbook.

Dave Peterson wrote:
So you want to pick out everything to the left of the first comma?

Dim myName As String
Dim FirstCommaPos As Long
'....stuff that does the copy
With ActiveSheet.Range("C1")
FirstCommaPos = InStr(1, .Value & ",", ",", vbTextCompare)
myName = Left(.Value, FirstCommaPos - 1)
End With

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myName & ".xls"

The value of C1 is not always John Smith. it's always this format "First Name
last name, date of birth"
[quoted text clipped - 23 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

Sorry I did not answer your last question:
So you want to pick out everything to the left of the first comma?

Yes.

Thanks
ielmrani wrote:
I put the following but it's working:

Sub SaveFile()

Dim myName As String
Dim FirstCommaPos As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Report").Copy

With ActiveSheet.Range("B1")
FirstCommaPos = InStr(1, .Value & ",", ",", vbTextCompare)
myName = Left(.Value, FirstCommaPos - 1)

End With

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myName & ".xls"

End Sub

I get this message: run-time error 1004 in Excel

one I bypass the error it create a new workbook.

So you want to pick out everything to the left of the first comma?

[quoted text clipped - 13 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

Is it the .saveas that gives you the runtime error?

If yes, then what's in B1 of that activesheet (did you really mean C1 in your
earlier post???).

Maybe you could look for the error:

on error resume next
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myName & ".xls"
if err.number < 0 then
msgbox "SaveAs failed: " & vblf & err.number & vblf & err.description
err.clear
else
msgbox "Ok"
end if
On error goto 0

=======
Maybe myName isn't what you expected.

Msgbox MyName

before the save may be a way to double check.

"ielmrani via OfficeKB.com" wrote:

I put the following but it's working:

Sub SaveFile()

Dim myName As String
Dim FirstCommaPos As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Report").Copy

With ActiveSheet.Range("B1")
FirstCommaPos = InStr(1, .Value & ",", ",", vbTextCompare)
myName = Left(.Value, FirstCommaPos - 1)

End With

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myName & ".xls"

End Sub

I get this message: run-time error 1004 in Excel

one I bypass the error it create a new workbook.

Dave Peterson wrote:
So you want to pick out everything to the left of the first comma?

Dim myName As String
Dim FirstCommaPos As Long
'....stuff that does the copy
With ActiveSheet.Range("C1")
FirstCommaPos = InStr(1, .Value & ",", ",", vbTextCompare)
myName = Left(.Value, FirstCommaPos - 1)
End With

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myName & ".xls"

The value of C1 is not always John Smith. it's always this format "First Name
last name, date of birth"
[quoted text clipped - 23 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

--

Dave Peterson