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 !

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 !

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 !

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 !

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 !

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 !

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 !

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 !