Save excel sheet

  • Thread starter ielmrani via OfficeKB.com
  • Start date
I

ielmrani via OfficeKB.com

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
 
D

Dave Peterson

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

becomes

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

ielmrani via OfficeKB.com

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 said:
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
Click to expand...
[quoted text clipped - 22 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200806/1
Click to expand...
Click to expand...
 
D

Dave Peterson

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 said:
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 said:
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
Click to expand...
[quoted text clipped - 22 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200806/1
Click to expand...
Click to expand...
Click to expand...
 
I

ielmrani via OfficeKB.com

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 said:
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"
Click to expand...
[quoted text clipped - 23 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200806/1
Click to expand...
Click to expand...
 
I

ielmrani via OfficeKB.com

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

Yes.

Thanks said:
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?
Click to expand...
[quoted text clipped - 13 lines]
Click to expand...
 
D

Dave Peterson

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 said:
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 said:
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"
Click to expand...
[quoted text clipped - 23 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200806/1
Click to expand...
Click to expand...
Click to expand...
 
I

ielmrani via OfficeKB.com

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 said:
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:
Click to expand...
[quoted text clipped - 42 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200806/1
Click to expand...
Click to expand...
 
I

ielmrani via OfficeKB.com

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 said:
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?
Click to expand...
[quoted text clipped - 25 lines]
Click to expand...
 
D

Dave Peterson

First, you may be able modify the code a bit:

oRow = oRow + 2
With rptwks.Cells(oRow, "B").Resize(1, 8)
.Value _
= Array("COMPANY", _
"POLICY" & vbLf & "NUMBER", _
"ISSUE" & vbLf & "DATE", _
"FACE" & vbLf & "AMOUNT", _
"TYPE", _
"ANNUAL" & vbLf & "PREMIUM", _
"SURRENDER VALUE" & vbLf & "AMOUNT", _
"SURRENDER VALUE" & vbLf & "DATE")
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
.EntireColumn.ColumnWidth = 200
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
End With

=====
As for some of the values getting the bold underline, maybe it's a setting
you're using.

In xl2003:
Tools|Options|Uncheck "Extend data range formats and formulas"

If that's not the problem, post back with a few more details.



ielmrani via OfficeKB.com said:
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 said:
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?
Click to expand...
[quoted text clipped - 25 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200806/1
Click to expand...
Click to expand...
Click to expand...
 
I

ielmrani via OfficeKB.com

I did this: Tools|Options|Uncheck "Extend data range formats and formulas"
but no change.

It seem to do it on the third line of the data.

this saga does not end, I was just asked to make this change:
The policy number, issue date, annual premium are put in the same cell. Is
there a way to put them on top of each other in different cells

For example:

Cel12 policy Issue etc
Cell13 number Date

I hope this make sense.

thanks

Dave said:
First, you may be able modify the code a bit:

oRow = oRow + 2
With rptwks.Cells(oRow, "B").Resize(1, 8)
.Value _
= Array("COMPANY", _
"POLICY" & vbLf & "NUMBER", _
"ISSUE" & vbLf & "DATE", _
"FACE" & vbLf & "AMOUNT", _
"TYPE", _
"ANNUAL" & vbLf & "PREMIUM", _
"SURRENDER VALUE" & vbLf & "AMOUNT", _
"SURRENDER VALUE" & vbLf & "DATE")
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
.EntireColumn.ColumnWidth = 200
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
End With

=====
As for some of the values getting the bold underline, maybe it's a setting
you're using.

In xl2003:
Tools|Options|Uncheck "Extend data range formats and formulas"

If that's not the problem, post back with a few more details.
I took the easy way and I create a new macro:
Sheets("Report").Select
Click to expand...
[quoted text clipped - 109 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200806/1
Click to expand...
Click to expand...
 
I

ielmrani via OfficeKB.com

Dave,
Never mind the extra bolding and underlining, it's working. Thanks
I did this: Tools|Options|Uncheck "Extend data range formats and formulas"
but no change.

It seem to do it on the third line of the data.

this saga does not end, I was just asked to make this change:
The policy number, issue date, annual premium are put in the same cell. Is
there a way to put them on top of each other in different cells

For example:

Cel12 policy Issue etc
Cell13 number Date

I hope this make sense.

thanks
First, you may be able modify the code a bit:
Click to expand...
[quoted text clipped - 30 lines]
Click to expand...
 
D

Dave Peterson

I don't understand.

The policy number is in column C.

And this plops the value from one cell into column C of that row:
RptWks.Cells(oRow, "B").Value = "'" & .Cells(iRow, "C").Value

It looks like one cell to me.

ielmrani via OfficeKB.com said:
I did this: Tools|Options|Uncheck "Extend data range formats and formulas"
but no change.

It seem to do it on the third line of the data.

this saga does not end, I was just asked to make this change:
The policy number, issue date, annual premium are put in the same cell. Is
there a way to put them on top of each other in different cells

For example:

Cel12 policy Issue etc
Cell13 number Date

I hope this make sense.

thanks

Dave said:
First, you may be able modify the code a bit:

oRow = oRow + 2
With rptwks.Cells(oRow, "B").Resize(1, 8)
.Value _
= Array("COMPANY", _
"POLICY" & vbLf & "NUMBER", _
"ISSUE" & vbLf & "DATE", _
"FACE" & vbLf & "AMOUNT", _
"TYPE", _
"ANNUAL" & vbLf & "PREMIUM", _
"SURRENDER VALUE" & vbLf & "AMOUNT", _
"SURRENDER VALUE" & vbLf & "DATE")
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
.EntireColumn.ColumnWidth = 200
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
End With

=====
As for some of the values getting the bold underline, maybe it's a setting
you're using.

In xl2003:
Tools|Options|Uncheck "Extend data range formats and formulas"

If that's not the problem, post back with a few more details.
I took the easy way and I create a new macro:
Sheets("Report").Select
Click to expand...
[quoted text clipped - 109 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200806/1
Click to expand...
Click to expand...
Click to expand...
 
I

ielmrani via OfficeKB.com

Yes the policy is in Column C and when the file is exported it it drops in
column B in a wrapped cells. I was asking if I could export it to 2
different cells. Policy in one cell and number in another cell, one on the
top of the other. I hope this is clear.

Dave said:
I don't understand.

The policy number is in column C.

And this plops the value from one cell into column C of that row:
RptWks.Cells(oRow, "B").Value = "'" & .Cells(iRow, "C").Value

It looks like one cell to me.
I did this: Tools|Options|Uncheck "Extend data range formats and formulas"
but no change.
Click to expand...
[quoted text clipped - 52 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200806/1
Click to expand...
Click to expand...
 
D

Dave Peterson

I still don't understand.

But you've seen how to set up the headers, so you can modify that portion.

This is the part that puts the values in:

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


Maybe you can change that to do what you want.

ielmrani via OfficeKB.com said:
Yes the policy is in Column C and when the file is exported it it drops in
column B in a wrapped cells. I was asking if I could export it to 2
different cells. Policy in one cell and number in another cell, one on the
top of the other. I hope this is clear.

Dave said:
I don't understand.

The policy number is in column C.

And this plops the value from one cell into column C of that row:
RptWks.Cells(oRow, "B").Value = "'" & .Cells(iRow, "C").Value

It looks like one cell to me.
I did this: Tools|Options|Uncheck "Extend data range formats and formulas"
but no change.
Click to expand...
[quoted text clipped - 52 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200806/1
Click to expand...
Click to expand...
Click to expand...
 
I

ielmrani via OfficeKB.com

Thank you

Dave said:
I still don't understand.

But you've seen how to set up the headers, so you can modify that portion.

This is the part that puts the values in:

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


Maybe you can change that to do what you want.
Yes the policy is in Column C and when the file is exported it it drops in
column B in a wrapped cells. I was asking if I could export it to 2
Click to expand...
[quoted text clipped - 19 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200806/1
Click to expand...
Click to expand...
 
Top