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

Sorry about the confusion, I did put C1 but the right cell is B1. when the
error comes out it highlight this line:
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myName & ".xls"

Dave Peterson wrote:
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.

I put the following but it's working:

[quoted text clipped - 42 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

I took the easy way and I create a new macro:
Sheets("Report").Select
Sheets("Report").Copy
and then I rename it. It's doing what I want. Thanks

I do have a different question, of course you remember the last code you help
me with. It's giving me a small problem. I want to bold and underline the
heading but sometimes it bold and underline some of the data do you know why?
thanks

As you can see I am an expert already "just jocking". But I learnt so much
thanks to you.

Here is my final code:

Sub GroupReport()
Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set CurWks = Worksheets("PreData")
Set RptWks = Worksheets("Report")

RptWks.Select
RptWks.Name = "Report"
Cells.Select
Selection.ClearContents
Range("A1").Select

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = -1
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then

'same group, do nothing special
Else
'different group, do headers
oRow = oRow + 2
RptWks.Cells(oRow, "A").Value _
= "Owner: " & .Cells(iRow, "A").Value

oRow = oRow + 1
RptWks.Cells(oRow, "A").Value _
= "Beneficiary: " & .Cells(iRow, "B").Value

oRow = oRow + 2
RptWks.Cells(oRow, "B").Value = "COMPANY"
RptWks.Cells(oRow, "B").Font.Bold = True
RptWks.Cells(oRow, "B").Font.Underline = xlUnderlineStyleSingle
RptWks.Cells(oRow, "C").Value = "POLICY" & vbLf & "NUMBER"
RptWks.Cells(oRow, "c").Font.Bold = True
RptWks.Cells(oRow, "c").Font.Underline = xlUnderlineStyleSingle
RptWks.Cells(oRow, "D").Value = "ISSUE" & vbLf & "DATE"
RptWks.Cells(oRow, "D").Font.Bold = True
RptWks.Cells(oRow, "D").Font.Underline = xlUnderlineStyleSingle
RptWks.Cells(oRow, "E").Value = "FACE" & vbLf & "AMOUNT"
RptWks.Cells(oRow, "E").Font.Bold = True
RptWks.Cells(oRow, "E").Font.Underline = xlUnderlineStyleSingle
RptWks.Cells(oRow, "F").Value = "TYPE"
RptWks.Cells(oRow, "F").Font.Bold = True
RptWks.Cells(oRow, "F").Font.Underline = xlUnderlineStyleSingle
RptWks.Cells(oRow, "G").Value = "ANNUAL" & vbLf & "PREMIUM"
RptWks.Cells(oRow, "G").Font.Bold = True
RptWks.Cells(oRow, "G").Font.Underline = xlUnderlineStyleSingle
RptWks.Cells(oRow, "H").Value = "SURRENDER VALUE" & vbLf &
"AMOUNT"
RptWks.Cells(oRow, "H").Font.Bold = True
RptWks.Cells(oRow, "H").Font.Underline = xlUnderlineStyleSingle
RptWks.Cells(oRow, "I").Value = "SURRENDER VALUE" & vbLf &
"DATE"
RptWks.Cells(oRow, "I").Font.Bold = True
RptWks.Cells(oRow, "I").Font.Underline = xlUnderlineStyleSingle
'Format Columns


End If

'do the policy stuff
oRow = oRow + 1
RptWks.Cells(oRow, "B").Value = "'" & .Cells(iRow, "C").Value
RptWks.Cells(oRow, "C").Value = "'" & .Cells(iRow, "D").Value
RptWks.Cells(oRow, "D").Value = "'" & .Cells(iRow, "E").Value
RptWks.Cells(oRow, "E").Value = .Cells(iRow, "F").Value
RptWks.Cells(oRow, "F").Value = "'" & .Cells(iRow, "G").Value
RptWks.Cells(oRow, "G").Value = .Cells(iRow, "H").Value
RptWks.Cells(oRow, "H").Value = .Cells(iRow, "I").Value
RptWks.Cells(oRow, "I").Value = "'" & .Cells(iRow, "J").Value

RptWks.Cells(oRow, "E").NumberFormat = "#,000"
RptWks.Cells(oRow, "G").NumberFormat = "#,000"
RptWks.Cells(oRow, "H").NumberFormat = "#,000"
Next iRow
End With


End Sub
ielmrani wrote:
Sorry about the confusion, I did put C1 but the right cell is B1. when the
error comes out it highlight this line:
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myName & ".xls"

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

[quoted text clipped - 25 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