| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy cells content only
Using 2007 Excell and trying to send data to a child sheet
Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
One way without using copy:
Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
additional info: To use the FillAcrossSheets method, you would have to set
an array for the sheets you want to include in the action, or specify all the sheets like this: Workbooks(1).Sheets.FillAcrossSheets Sheets(1).Range("B2").CurrentRegion You could then add the constant xlFillWithContents if need be. "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
I have tried your suggestion without any luck. Im sure I have not done it
correctly I have some actual code for your evaluation; Sub copystuff() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") '.Resize(1, 3) Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") End Sub Remember I do not want the formatting or the formulas, only the values. Thanks for your time Chris "JLGWhiz" wrote: One way without using copy: Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
You can change these lines:
ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") To this: ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues This is the second syntax example I gave you for copying. The only requirement is that there be at least the same amount of cells and shape to the receiving range. "cgnotrom" wrote: I have tried your suggestion without any luck. Im sure I have not done it correctly I have some actual code for your evaluation; Sub copystuff() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") '.Resize(1, 3) Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") End Sub Remember I do not want the formatting or the formulas, only the values. Thanks for your time Chris "JLGWhiz" wrote: One way without using copy: Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
THANKYOU I Will try this and get back to you
I put the example assigned to the Range selection the first time. "JLGWhiz" wrote: You can change these lines: ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") To this: ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues This is the second syntax example I gave you for copying. The only requirement is that there be at least the same amount of cells and shape to the receiving range. "cgnotrom" wrote: I have tried your suggestion without any luck. Im sure I have not done it correctly I have some actual code for your evaluation; Sub copystuff() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") '.Resize(1, 3) Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") End Sub Remember I do not want the formatting or the formulas, only the values. Thanks for your time Chris "JLGWhiz" wrote: One way without using copy: Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
I was forced to change the statement as follows; ..PasteSpecial(Paste:=xlPasteValues) but that gives me the following; Runtime error '1004': "Unable to get the PasteSpecial Property of the Range class" By now you know I lost.......Hope your not ! Chris ps. Am I keeping you awake...... "JLGWhiz" wrote: You can change these lines: ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") To this: ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues This is the second syntax example I gave you for copying. The only requirement is that there be at least the same amount of cells and shape to the receiving range. "cgnotrom" wrote: I have tried your suggestion without any luck. Im sure I have not done it correctly I have some actual code for your evaluation; Sub copystuff() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") '.Resize(1, 3) Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") End Sub Remember I do not want the formatting or the formulas, only the values. Thanks for your time Chris "JLGWhiz" wrote: One way without using copy: Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
The parentheses are not necessary and in fact are probably causing the problem.
The code should work exactly as I gave it to you unless the range you are copying is a larger range than the one you are trying to paste to. Your code shows this line: ltrow = recap.Range("D3") + 13 That makes ltrow = 13. Is this what you expected? If you want verify this then insert this line just below that one: MsgBox ltrow The message box will show you the actual value of ltrow. If I am correct then you are trying to post to cells F13, N13, O13 and P13. Are these the correct cells? "cgnotrom" wrote: expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose) I was forced to change the statement as follows; .PasteSpecial(Paste:=xlPasteValues) but that gives me the following; Runtime error '1004': "Unable to get the PasteSpecial Property of the Range class" By now you know I lost.......Hope your not ! Chris ps. Am I keeping you awake...... "JLGWhiz" wrote: You can change these lines: ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") To this: ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues This is the second syntax example I gave you for copying. The only requirement is that there be at least the same amount of cells and shape to the receiving range. "cgnotrom" wrote: I have tried your suggestion without any luck. Im sure I have not done it correctly I have some actual code for your evaluation; Sub copystuff() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") '.Resize(1, 3) Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") End Sub Remember I do not want the formatting or the formulas, only the values. Thanks for your time Chris "JLGWhiz" wrote: One way without using copy: Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
("D3") actually has a value of 1 so ltrow =14...d3 is incremented manually
each week so next week data goes to row 15 When I remove the parentheses I get an "Compile error: 'Expected: end of statement' as long as I am not trying to move the values of a formula I get numbers as expected,but still the formatting is also transfered. "JLGWhiz" wrote: The parentheses are not necessary and in fact are probably causing the problem. The code should work exactly as I gave it to you unless the range you are copying is a larger range than the one you are trying to paste to. Your code shows this line: ltrow = recap.Range("D3") + 13 That makes ltrow = 13. Is this what you expected? If you want verify this then insert this line just below that one: MsgBox ltrow The message box will show you the actual value of ltrow. If I am correct then you are trying to post to cells F13, N13, O13 and P13. Are these the correct cells? "cgnotrom" wrote: expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose) I was forced to change the statement as follows; .PasteSpecial(Paste:=xlPasteValues) but that gives me the following; Runtime error '1004': "Unable to get the PasteSpecial Property of the Range class" By now you know I lost.......Hope your not ! Chris ps. Am I keeping you awake...... "JLGWhiz" wrote: You can change these lines: ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") To this: ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues This is the second syntax example I gave you for copying. The only requirement is that there be at least the same amount of cells and shape to the receiving range. "cgnotrom" wrote: I have tried your suggestion without any luck. Im sure I have not done it correctly I have some actual code for your evaluation; Sub copystuff() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") '.Resize(1, 3) Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") End Sub Remember I do not want the formatting or the formulas, only the values. Thanks for your time Chris "JLGWhiz" wrote: One way without using copy: Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
Copy what you have and paste it to the reply window so I can edit it. You
should not be getting that from the code I gave you. "cgnotrom" wrote: ("D3") actually has a value of 1 so ltrow =14...d3 is incremented manually each week so next week data goes to row 15 When I remove the parentheses I get an "Compile error: 'Expected: end of statement' as long as I am not trying to move the values of a formula I get numbers as expected,but still the formatting is also transfered. "JLGWhiz" wrote: The parentheses are not necessary and in fact are probably causing the problem. The code should work exactly as I gave it to you unless the range you are copying is a larger range than the one you are trying to paste to. Your code shows this line: ltrow = recap.Range("D3") + 13 That makes ltrow = 13. Is this what you expected? If you want verify this then insert this line just below that one: MsgBox ltrow The message box will show you the actual value of ltrow. If I am correct then you are trying to post to cells F13, N13, O13 and P13. Are these the correct cells? "cgnotrom" wrote: expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose) I was forced to change the statement as follows; .PasteSpecial(Paste:=xlPasteValues) but that gives me the following; Runtime error '1004': "Unable to get the PasteSpecial Property of the Range class" By now you know I lost.......Hope your not ! Chris ps. Am I keeping you awake...... "JLGWhiz" wrote: You can change these lines: ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") To this: ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues This is the second syntax example I gave you for copying. The only requirement is that there be at least the same amount of cells and shape to the receiving range. "cgnotrom" wrote: I have tried your suggestion without any luck. Im sure I have not done it correctly I have some actual code for your evaluation; Sub copystuff() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") '.Resize(1, 3) Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") End Sub Remember I do not want the formatting or the formulas, only the values. Thanks for your time Chris "JLGWhiz" wrote: One way without using copy: Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
After you first copied everything over, you changed the formatting for those
cells. You will have to re-format them to get rid of what you copied before. Then the PasteSpecial Paste:=xlPasteValues should only post the cell content with the format of the receiving cell. "cgnotrom" wrote: ("D3") actually has a value of 1 so ltrow =14...d3 is incremented manually each week so next week data goes to row 15 When I remove the parentheses I get an "Compile error: 'Expected: end of statement' as long as I am not trying to move the values of a formula I get numbers as expected,but still the formatting is also transfered. "JLGWhiz" wrote: The parentheses are not necessary and in fact are probably causing the problem. The code should work exactly as I gave it to you unless the range you are copying is a larger range than the one you are trying to paste to. Your code shows this line: ltrow = recap.Range("D3") + 13 That makes ltrow = 13. Is this what you expected? If you want verify this then insert this line just below that one: MsgBox ltrow The message box will show you the actual value of ltrow. If I am correct then you are trying to post to cells F13, N13, O13 and P13. Are these the correct cells? "cgnotrom" wrote: expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose) I was forced to change the statement as follows; .PasteSpecial(Paste:=xlPasteValues) but that gives me the following; Runtime error '1004': "Unable to get the PasteSpecial Property of the Range class" By now you know I lost.......Hope your not ! Chris ps. Am I keeping you awake...... "JLGWhiz" wrote: You can change these lines: ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") To this: ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues This is the second syntax example I gave you for copying. The only requirement is that there be at least the same amount of cells and shape to the receiving range. "cgnotrom" wrote: I have tried your suggestion without any luck. Im sure I have not done it correctly I have some actual code for your evaluation; Sub copystuff() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") '.Resize(1, 3) Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") End Sub Remember I do not want the formatting or the formulas, only the values. Thanks for your time Chris "JLGWhiz" wrote: One way without using copy: Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
Hereis the actual sub
Sub copyteam() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues is the actual End sub The paste Special still fails....Not sure what formatting needs changing. I cleared the data from the receiving sheet. Chris "JLGWhiz" wrote: After you first copied everything over, you changed the formatting for those cells. You will have to re-format them to get rid of what you copied before. Then the PasteSpecial Paste:=xlPasteValues should only post the cell content with the format of the receiving cell. "cgnotrom" wrote: ("D3") actually has a value of 1 so ltrow =14...d3 is incremented manually each week so next week data goes to row 15 When I remove the parentheses I get an "Compile error: 'Expected: end of statement' as long as I am not trying to move the values of a formula I get numbers as expected,but still the formatting is also transfered. "JLGWhiz" wrote: The parentheses are not necessary and in fact are probably causing the problem. The code should work exactly as I gave it to you unless the range you are copying is a larger range than the one you are trying to paste to. Your code shows this line: ltrow = recap.Range("D3") + 13 That makes ltrow = 13. Is this what you expected? If you want verify this then insert this line just below that one: MsgBox ltrow The message box will show you the actual value of ltrow. If I am correct then you are trying to post to cells F13, N13, O13 and P13. Are these the correct cells? "cgnotrom" wrote: expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose) I was forced to change the statement as follows; .PasteSpecial(Paste:=xlPasteValues) but that gives me the following; Runtime error '1004': "Unable to get the PasteSpecial Property of the Range class" By now you know I lost.......Hope your not ! Chris ps. Am I keeping you awake...... "JLGWhiz" wrote: You can change these lines: ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") To this: ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues This is the second syntax example I gave you for copying. The only requirement is that there be at least the same amount of cells and shape to the receiving range. "cgnotrom" wrote: I have tried your suggestion without any luck. Im sure I have not done it correctly I have some actual code for your evaluation; Sub copystuff() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") '.Resize(1, 3) Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") End Sub Remember I do not want the formatting or the formulas, only the values. Thanks for your time Chris "JLGWhiz" wrote: One way without using copy: Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
Don't try to re-type this. Copy it directly from the
screen and Paste it into your code window. Clean up your formatting on Row 14 of Sheets(lt) before you run the macro again. I don't see anything in this code that would cause an error message because of the code itself. You should get only the source cell contents pasted. Sub copyteam() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues End sub "cgnotrom" wrote: Hereis the actual sub Sub copyteam() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues is the actual End sub The paste Special still fails....Not sure what formatting needs changing. I cleared the data from the receiving sheet. Chris "JLGWhiz" wrote: After you first copied everything over, you changed the formatting for those cells. You will have to re-format them to get rid of what you copied before. Then the PasteSpecial Paste:=xlPasteValues should only post the cell content with the format of the receiving cell. "cgnotrom" wrote: ("D3") actually has a value of 1 so ltrow =14...d3 is incremented manually each week so next week data goes to row 15 When I remove the parentheses I get an "Compile error: 'Expected: end of statement' as long as I am not trying to move the values of a formula I get numbers as expected,but still the formatting is also transfered. "JLGWhiz" wrote: The parentheses are not necessary and in fact are probably causing the problem. The code should work exactly as I gave it to you unless the range you are copying is a larger range than the one you are trying to paste to. Your code shows this line: ltrow = recap.Range("D3") + 13 That makes ltrow = 13. Is this what you expected? If you want verify this then insert this line just below that one: MsgBox ltrow The message box will show you the actual value of ltrow. If I am correct then you are trying to post to cells F13, N13, O13 and P13. Are these the correct cells? "cgnotrom" wrote: expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose) I was forced to change the statement as follows; .PasteSpecial(Paste:=xlPasteValues) but that gives me the following; Runtime error '1004': "Unable to get the PasteSpecial Property of the Range class" By now you know I lost.......Hope your not ! Chris ps. Am I keeping you awake...... "JLGWhiz" wrote: You can change these lines: ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") To this: ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues This is the second syntax example I gave you for copying. The only requirement is that there be at least the same amount of cells and shape to the receiving range. "cgnotrom" wrote: I have tried your suggestion without any luck. Im sure I have not done it correctly I have some actual code for your evaluation; Sub copystuff() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") '.Resize(1, 3) Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") End Sub Remember I do not want the formatting or the formulas, only the values. Thanks for your time Chris "JLGWhiz" wrote: One way without using copy: Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
BTW when using PasteSpecial, the destination line cannot be on the same line
as the copy command. That is why you were getting the 'Expected end of statement.' message. If you are copying everything over and you only use the destination range without any frills, then you can put it all on the same line. "cgnotrom" wrote: Hereis the actual sub Sub copyteam() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues is the actual End sub The paste Special still fails....Not sure what formatting needs changing. I cleared the data from the receiving sheet. Chris "JLGWhiz" wrote: After you first copied everything over, you changed the formatting for those cells. You will have to re-format them to get rid of what you copied before. Then the PasteSpecial Paste:=xlPasteValues should only post the cell content with the format of the receiving cell. "cgnotrom" wrote: ("D3") actually has a value of 1 so ltrow =14...d3 is incremented manually each week so next week data goes to row 15 When I remove the parentheses I get an "Compile error: 'Expected: end of statement' as long as I am not trying to move the values of a formula I get numbers as expected,but still the formatting is also transfered. "JLGWhiz" wrote: The parentheses are not necessary and in fact are probably causing the problem. The code should work exactly as I gave it to you unless the range you are copying is a larger range than the one you are trying to paste to. Your code shows this line: ltrow = recap.Range("D3") + 13 That makes ltrow = 13. Is this what you expected? If you want verify this then insert this line just below that one: MsgBox ltrow The message box will show you the actual value of ltrow. If I am correct then you are trying to post to cells F13, N13, O13 and P13. Are these the correct cells? "cgnotrom" wrote: expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose) I was forced to change the statement as follows; .PasteSpecial(Paste:=xlPasteValues) but that gives me the following; Runtime error '1004': "Unable to get the PasteSpecial Property of the Range class" By now you know I lost.......Hope your not ! Chris ps. Am I keeping you awake...... "JLGWhiz" wrote: You can change these lines: ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") To this: ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues This is the second syntax example I gave you for copying. The only requirement is that there be at least the same amount of cells and shape to the receiving range. "cgnotrom" wrote: I have tried your suggestion without any luck. Im sure I have not done it correctly I have some actual code for your evaluation; Sub copystuff() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") '.Resize(1, 3) Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") End Sub Remember I do not want the formatting or the formulas, only the values. Thanks for your time Chris "JLGWhiz" wrote: One way without using copy: Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
JLGWhiz,
Thankyou so much..The only change I see that you made was seperating the 'ltgms.copy' from the xfer statements. and it works perfectly. I must have some stray typos or something Thankyou again..you went above and beyond with this one. Chris "JLGWhiz" wrote: Don't try to re-type this. Copy it directly from the screen and Paste it into your code window. Clean up your formatting on Row 14 of Sheets(lt) before you run the macro again. I don't see anything in this code that would cause an error message because of the code itself. You should get only the source cell contents pasted. Sub copyteam() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues End sub "cgnotrom" wrote: Hereis the actual sub Sub copyteam() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues is the actual End sub The paste Special still fails....Not sure what formatting needs changing. I cleared the data from the receiving sheet. Chris "JLGWhiz" wrote: After you first copied everything over, you changed the formatting for those cells. You will have to re-format them to get rid of what you copied before. Then the PasteSpecial Paste:=xlPasteValues should only post the cell content with the format of the receiving cell. "cgnotrom" wrote: ("D3") actually has a value of 1 so ltrow =14...d3 is incremented manually each week so next week data goes to row 15 When I remove the parentheses I get an "Compile error: 'Expected: end of statement' as long as I am not trying to move the values of a formula I get numbers as expected,but still the formatting is also transfered. "JLGWhiz" wrote: The parentheses are not necessary and in fact are probably causing the problem. The code should work exactly as I gave it to you unless the range you are copying is a larger range than the one you are trying to paste to. Your code shows this line: ltrow = recap.Range("D3") + 13 That makes ltrow = 13. Is this what you expected? If you want verify this then insert this line just below that one: MsgBox ltrow The message box will show you the actual value of ltrow. If I am correct then you are trying to post to cells F13, N13, O13 and P13. Are these the correct cells? "cgnotrom" wrote: expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose) I was forced to change the statement as follows; .PasteSpecial(Paste:=xlPasteValues) but that gives me the following; Runtime error '1004': "Unable to get the PasteSpecial Property of the Range class" By now you know I lost.......Hope your not ! Chris ps. Am I keeping you awake...... "JLGWhiz" wrote: You can change these lines: ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") To this: ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues This is the second syntax example I gave you for copying. The only requirement is that there be at least the same amount of cells and shape to the receiving range. "cgnotrom" wrote: I have tried your suggestion without any luck. Im sure I have not done it correctly I have some actual code for your evaluation; Sub copystuff() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") '.Resize(1, 3) Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") End Sub Remember I do not want the formatting or the formulas, only the values. Thanks for your time Chris "JLGWhiz" wrote: One way without using copy: Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to copy cells content only
I will remember that bit of info, as I plan on expanding this spreadsheet
greatly expect to have 320 records to post to It past midnight here so again Thankyou Chris "JLGWhiz" wrote: BTW when using PasteSpecial, the destination line cannot be on the same line as the copy command. That is why you were getting the 'Expected end of statement.' message. If you are copying everything over and you only use the destination range without any frills, then you can put it all on the same line. "cgnotrom" wrote: Hereis the actual sub Sub copyteam() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues is the actual End sub The paste Special still fails....Not sure what formatting needs changing. I cleared the data from the receiving sheet. Chris "JLGWhiz" wrote: After you first copied everything over, you changed the formatting for those cells. You will have to re-format them to get rid of what you copied before. Then the PasteSpecial Paste:=xlPasteValues should only post the cell content with the format of the receiving cell. "cgnotrom" wrote: ("D3") actually has a value of 1 so ltrow =14...d3 is incremented manually each week so next week data goes to row 15 When I remove the parentheses I get an "Compile error: 'Expected: end of statement' as long as I am not trying to move the values of a formula I get numbers as expected,but still the formatting is also transfered. "JLGWhiz" wrote: The parentheses are not necessary and in fact are probably causing the problem. The code should work exactly as I gave it to you unless the range you are copying is a larger range than the one you are trying to paste to. Your code shows this line: ltrow = recap.Range("D3") + 13 That makes ltrow = 13. Is this what you expected? If you want verify this then insert this line just below that one: MsgBox ltrow The message box will show you the actual value of ltrow. If I am correct then you are trying to post to cells F13, N13, O13 and P13. Are these the correct cells? "cgnotrom" wrote: expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose) I was forced to change the statement as follows; .PasteSpecial(Paste:=xlPasteValues) but that gives me the following; Runtime error '1004': "Unable to get the PasteSpecial Property of the Range class" By now you know I lost.......Hope your not ! Chris ps. Am I keeping you awake...... "JLGWhiz" wrote: You can change these lines: ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") To this: ltgms.Copy ltrec.Cells(ltrow, "F").PasteSpecial Paste:=xlPasteValues lhcp.Copy ltrec.Cells(ltrow, "N").PasteSpecial Paste:=xlPasteValues lwon.Copy ltrec.Cells(ltrow, "O").PasteSpecial Paste:=xlPasteValues llost.Copy ltrec.Cells(ltrow, "P").PasteSpecial Paste:=xlPasteValues This is the second syntax example I gave you for copying. The only requirement is that there be at least the same amount of cells and shape to the receiving range. "cgnotrom" wrote: I have tried your suggestion without any luck. Im sure I have not done it correctly I have some actual code for your evaluation; Sub copystuff() Dim lt As String Set recap = Sheets("Input") Set ltgms = Sheets("Input").Range("M15:O15") '.Resize(1, 3) Set lhcp = Sheets("Input").Range("L14") Set lwon = Sheets("Input").Range("K19") Set llost = Sheets("Input").Range("K20") ltrec = 10 * recap.Cells(4, 4).Value lt = ltrec Set ltrec = Sheets(lt) ltrow = recap.Range("D3") + 13 ltgms.Copy ltrec.Cells(ltrow, "F") lhcp.Copy ltrec.Cells(ltrow, "N") lwon.Copy ltrec.Cells(ltrow, "O") llost.Copy ltrec.Cells(ltrow, "P") End Sub Remember I do not want the formatting or the formulas, only the values. Thanks for your time Chris "JLGWhiz" wrote: One way without using copy: Sheets(2).Range("B5") = Sheets(1).Range("D10").Value Using copy: Sheets(1).Range("D10").Copy Sheets(2).Range("B5").PasteSpecial Paste:=xlPasteValues "cgnotrom" wrote: Using 2007 Excell and trying to send data to a child sheet Currently I am getting the Formula and Colors and Borders. I only want the Value result of the formula to be copyed to the next sheet, not the Formula. example of a command I am using; Set lg = Sheets("mine").Cells(6,13).Resize(1,3) tryed to enter the statement Type:=xlFillWithContents but the debugger didn't like me. Please help ! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy linked cells content to an other workbook | Excel Programming | |||
| how to copy content AND formating with vba | Excel Programming | |||
| copy comment content to cell content as data not as comment | Excel Discussion (Misc queries) | |||
| copy cell content only | Excel Programming | |||
| copy content of 1 sheet to another one. | Excel Programming | |||
Linear Mode
