Using VB Copy Entire Row but formulas and formats only no values

[Kenny]

I have a workbook called tracker that adds new records from a workbook called
data. When I add a new record I want to copy the formulas and formatting from
the row above and paste them into the empty row. Every time I try it copies
the values as well. please help

 

[macropod]

Hi Kenny,

If, after copying you can still see formulae in the cells you've pasted into, then the values you're seeing are most likely the
results of the formulae re-calculating. The only exceptions to this would be if recalc is set to manual or the cells are formatted
as text. If you're cells have formulae, you've got to expect something to be displayed (or a nul response if that's what the
expression evaluates to).

 

[Kenny]

No, when I rub my vb script that paste special formulas only it pastes values
as well

Hi Kenny,

If, after copying you can still see formulae in the cells you've pasted into, then the values you're seeing are most likely the
results of the formulae re-calculating. The only exceptions to this would be if recalc is set to manual or the cells are formatted
as text. If you're cells have formulae, you've got to expect something to be displayed (or a nul response if that's what the
expression evaluates to).

--
Cheers
macropod
[MVP - Microsoft Word]

I have a workbook called tracker that adds new records from a workbook called
data. When I add a new record I want to copy the formulas and formatting from
the row above and paste them into the empty row. Every time I try it copies
the values as well. please help

 

[macropod]

Hi Kenny,

I suggest you re-read my previous reply. When you paste a formula, the Excel evaluates the cell references that apply to the pasted
copy and calculates a new value. That new value may or may not be the same as the old one.

--
Cheers
macropod
[MVP - Microsoft Word]

No, when I rub my vb script that paste special formulas only it pastes values
as well

Hi Kenny,

If, after copying you can still see formulae in the cells you've pasted into, then the values you're seeing are most likely the
results of the formulae re-calculating. The only exceptions to this would be if recalc is set to manual or the cells are
formatted
as text. If you're cells have formulae, you've got to expect something to be displayed (or a nul response if that's what the
expression evaluates to).

--
Cheers
macropod
[MVP - Microsoft Word]

I have a workbook called tracker that adds new records from a workbook called
data. When I add a new record I want to copy the formulas and formatting from
the row above and paste them into the empty row. Every time I try it copies
the values as well. please help

 

[Kenny]

I apoligize maybe I did not explain this well enough. Let me try again. My
script pulls records from another workbook and pastes them into this
workbook. As it enters new records I want it to copy the formatting and
formulas from the rows above. In the rows I am copying from is DATA and
formulas. I am trying to copy the row and then special paste formulas but it
copys the formulas and DATA into cells in the next row. How can I write a
script that will only copy the formulas and formatting down to the new row.
Not any data.

Hi Kenny,

I suggest you re-read my previous reply. When you paste a formula, the Excel evaluates the cell references that apply to the pasted
copy and calculates a new value. That new value may or may not be the same as the old one.

--
Cheers
macropod
[MVP - Microsoft Word]

No, when I rub my vb script that paste special formulas only it pastes values
as well

Hi Kenny,

If, after copying you can still see formulae in the cells you've pasted into, then the values you're seeing are most likely the
results of the formulae re-calculating. The only exceptions to this would be if recalc is set to manual or the cells are
formatted
as text. If you're cells have formulae, you've got to expect something to be displayed (or a nul response if that's what the
expression evaluates to).

--
Cheers
macropod
[MVP - Microsoft Word]


I have a workbook called tracker that adds new records from a workbook called
data. When I add a new record I want to copy the formulas and formatting from
the row above and paste them into the empty row. Every time I try it copies
the values as well. please help

 

[macropod]

Hi Kenny,

If the source cell has only a value (ie no formula), then that's what Edit|Paste Special|Formulas will paste (in other words, the
value is treated as the formula). That's how it's supposed to work. If you want to do something different, you'll have to either use
two copy/paste operations (one for cells with formulae - using Edit|Paste Special|Formulas - and one for cells without - Edit|Paste
Special|Format) or code a macro to do much the same thing.

--
Cheers
macropod
[MVP - Microsoft Word]

I apoligize maybe I did not explain this well enough. Let me try again. My
script pulls records from another workbook and pastes them into this
workbook. As it enters new records I want it to copy the formatting and
formulas from the rows above. In the rows I am copying from is DATA and
formulas. I am trying to copy the row and then special paste formulas but it
copys the formulas and DATA into cells in the next row. How can I write a
script that will only copy the formulas and formatting down to the new row.
Not any data.

Hi Kenny,

I suggest you re-read my previous reply. When you paste a formula, the Excel evaluates the cell references that apply to the
pasted
copy and calculates a new value. That new value may or may not be the same as the old one.

--
Cheers
macropod
[MVP - Microsoft Word]

No, when I rub my vb script that paste special formulas only it pastes values
as well

:

Hi Kenny,

If, after copying you can still see formulae in the cells you've pasted into, then the values you're seeing are most likely
the
results of the formulae re-calculating. The only exceptions to this would be if recalc is set to manual or the cells are
formatted
as text. If you're cells have formulae, you've got to expect something to be displayed (or a nul response if that's what the
expression evaluates to).

--
Cheers
macropod
[MVP - Microsoft Word]


I have a workbook called tracker that adds new records from a workbook called
data. When I add a new record I want to copy the formulas and formatting from
the row above and paste them into the empty row. Every time I try it copies
the values as well. please help