Why does this work?
 

This works correctly

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow,
"B").Resize(1, 28).Value
End With

Why do I need the resize (1, 28) twice on the last line to change the
equations to values? But on the second line - I don't need it.

Confused - can someone shed some light on the subject. Or if there is a
better way to range value - please let me know.

Why does this work?
 

The first instance is a copy. When you copy a bunch of cells all you need to
do is to point to the single upper left cell where you would like to paste
the results and the paste will paste the entire copied range, starting at
that cell. Good so far? The next line is changing what was copied to values.
In this case we take the values of the entire range and want to put them back
in the same range. This is not a copy. You are making an array of the values
in the cells and putting that array into the range that it came from... That
is it in a nut shell...
--
HTH...

Jim Thomlinson


"Brad" wrote:

This works correctly

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow,
"B").Resize(1, 28).Value
End With

Why do I need the resize (1, 28) twice on the last line to change the
equations to values? But on the second line - I don't need it.

Confused - can someone shed some light on the subject. Or if there is a
better way to range value - please let me know.

Why does this work?
 

Because the resize determines the size of the range being acted upon. In the
first instance, copy only needs to know the size of the source range, it
will map that onto a similar sized target range. But the second instance is
simulating pastespecdial values, it needs to know the size of the source
range and the target range, otherwise it would not map correctly. For
instance

.Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow,
"B").Value

would copy the value of the first cell in that range to every cell in the
range, not what is wanted.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Brad" wrote in message
...
This works correctly

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow,
"B").Resize(1, 28).Value
End With

Why do I need the resize (1, 28) twice on the last line to change the
equations to values? But on the second line - I don't need it.

Confused - can someone shed some light on the subject. Or if there is a
better way to range value - please let me know.

Why does this work?
 

You received good explanations. here is an approach that doesn't need the
resize multiple times.

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy
.Cells(LastRow + 1, "B").PasteSpecial xlValues
.Cells(LastRow + 1, "B").PasteSpecial xlFormats
End With

--
Regards,
Tom Ogilvy





"Brad" wrote:

This works correctly

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow,
"B").Resize(1, 28).Value
End With

Why do I need the resize (1, 28) twice on the last line to change the
equations to values? But on the second line - I don't need it.

Confused - can someone shed some light on the subject. Or if there is a
better way to range value - please let me know.

Why does this work?
 

Thank you

"Jim Thomlinson" wrote:

The first instance is a copy. When you copy a bunch of cells all you need to
do is to point to the single upper left cell where you would like to paste
the results and the paste will paste the entire copied range, starting at
that cell. Good so far? The next line is changing what was copied to values.
In this case we take the values of the entire range and want to put them back
in the same range. This is not a copy. You are making an array of the values
in the cells and putting that array into the range that it came from... That
is it in a nut shell...
--
HTH...

Jim Thomlinson


"Brad" wrote:

This works correctly

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow,
"B").Resize(1, 28).Value
End With

Why do I need the resize (1, 28) twice on the last line to change the
equations to values? But on the second line - I don't need it.

Confused - can someone shed some light on the subject. Or if there is a
better way to range value - please let me know.

Why does this work?
 

As always - appreciate the help.

As you probably already noticed - I had to make one final tweek from
yesterday's help.

"Bob Phillips" wrote:

Because the resize determines the size of the range being acted upon. In the
first instance, copy only needs to know the size of the source range, it
will map that onto a similar sized target range. But the second instance is
simulating pastespecdial values, it needs to know the size of the source
range and the target range, otherwise it would not map correctly. For
instance

.Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow,
"B").Value

would copy the value of the first cell in that range to every cell in the
range, not what is wanted.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Brad" wrote in message
...
This works correctly

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow,
"B").Resize(1, 28).Value
End With

Why do I need the resize (1, 28) twice on the last line to change the
equations to values? But on the second line - I don't need it.

Confused - can someone shed some light on the subject. Or if there is a
better way to range value - please let me know.

Why does this work?
 

If I wanted to do this

1. I would have to change

..Cells(LastRow + 1, "B").PasteSpecial xlValues to
..Cells(LastRow, "B").PasteSpecial xlValues - to get rid of the formulas

and I would have to copy the equations to (LastRow + 1) before I do number 1

Correct?


"Tom Ogilvy" wrote:

You received good explanations. here is an approach that doesn't need the
resize multiple times.

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy
.Cells(LastRow + 1, "B").PasteSpecial xlValues
.Cells(LastRow + 1, "B").PasteSpecial xlFormats
End With

--
Regards,
Tom Ogilvy





"Brad" wrote:

This works correctly

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow,
"B").Resize(1, 28).Value
End With

Why do I need the resize (1, 28) twice on the last line to change the
equations to values? But on the second line - I don't need it.

Confused - can someone shed some light on the subject. Or if there is a
better way to range value - please let me know.

Why does this work?
 

LOL. I went the other way to avoid the pastespecial.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tom Ogilvy" wrote in message
...
You received good explanations. here is an approach that doesn't need the
resize multiple times.

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy
.Cells(LastRow + 1, "B").PasteSpecial xlValues
.Cells(LastRow + 1, "B").PasteSpecial xlFormats
End With

--
Regards,
Tom Ogilvy





"Brad" wrote:

This works correctly

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow,
"B").Resize(1, 28).Value
End With

Why do I need the resize (1, 28) twice on the last line to change the
equations to values? But on the second line - I don't need it.

Confused - can someone shed some light on the subject. Or if there is a
better way to range value - please let me know.

Why does this work?
 

My apologies, I did misread your intent. This is what I believe you are
doing.

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow, "B").Resize(1, 28).Copy
.Cells(LastRow, "B").PasteSpecial xlValues
End With

But is doesn't gain anything and may be worse because of the second copy -
and looking at bob's comment, I suspect he must have suggested the original
in an earlier thread.

--
Regards,
Tom Ogilvy


"Brad" wrote:

If I wanted to do this

1. I would have to change

.Cells(LastRow + 1, "B").PasteSpecial xlValues to
.Cells(LastRow, "B").PasteSpecial xlValues - to get rid of the formulas

and I would have to copy the equations to (LastRow + 1) before I do number 1

Correct?


"Tom Ogilvy" wrote:

You received good explanations. here is an approach that doesn't need the
resize multiple times.

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy
.Cells(LastRow + 1, "B").PasteSpecial xlValues
.Cells(LastRow + 1, "B").PasteSpecial xlFormats
End With

--
Regards,
Tom Ogilvy





"Brad" wrote:

This works correctly

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow, "B").Resize(1, 28).Value = .Cells(LastRow,
"B").Resize(1, 28).Value
End With

Why do I need the resize (1, 28) twice on the last line to change the
equations to values? But on the second line - I don't need it.

Confused - can someone shed some light on the subject. Or if there is a
better way to range value - please let me know.