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 !