| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save excel sheet
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
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" wrote: 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 -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
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 Peterson wrote: 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 [quoted text clipped - 109 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
Dave,
Never mind the extra bolding and underlining, it's working. Thanks ielmrani wrote: 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: [quoted text clipped - 30 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
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" wrote: 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 Peterson wrote: 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 [quoted text clipped - 109 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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
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 Peterson wrote: 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. [quoted text clipped - 52 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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
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" wrote: 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 Peterson wrote: 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. [quoted text clipped - 52 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 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Save excel sheet
Thank you
Dave Peterson wrote: 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 [quoted text clipped - 19 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Save, save as, page setup dimmed out in unprotected excel sheet? | Excel Discussion (Misc queries) | |||
| VBA code to save excel sheet | Excel Discussion (Misc queries) | |||
| Macro to save excel sheet in a workbook | Excel Discussion (Misc queries) | |||
| save from template to excel sheet | Excel Discussion (Misc queries) | |||
| create a macro to save excel sheet | Excel Worksheet Functions | |||
Linear Mode
