Copy as formula paste as formula

[drop05]

Hello I have two workbooks one in which i wish to copy from and the other i wish to paste into.

My source workbook some cells have formulas but some have values and i would like to paste as what they are in a tare workbook but the same way

So if source cell copying from is a formula then paste as a formula in target, rather than it copying the value the formula creates would like the formula to be pasted
however,
if source it is a value then paste as a value in target workbook

Is there a way to do so in VBA

 

[StephenCrump]

Am I missing something? A simple copy/paste will copy formula --> formula, and value --> value.

Perhaps you need to provide a little more information about what you're trying to do?

 

[drop05]

Am I missing something? A simple copy/paste will copy formula --> formula, and value --> value.

Perhaps you need to provide a little more information about what you're trying to do?

I suppose my main concern is say say cell A1 is a formula =A2+A3 and A2=1 and A3=1 so then A is 2, in the source, but say in target A2=2 and A3=2, i would want the formula from the source to be moved over so that way it is =A2+A3 rather than just pasting 2 that way it can run the formula and be 4.

Now cases where some workbooks may not have a formula in A1, so was not sure if having copy as formula would still work or give it an error so was not sure if there is an in between where if it is copying as a formula and paste as a formula if there is a formula, if there is no formula then it copies the value and pastes the value just every workbook would be different some may have a formula some may just have a value.

 

[StephenCrump]

You can write code that takes different actions depending on whether cells contain formulae or values.

Post #3 implies you want to leave the destination cells A2 and A3 unchanged. Do you want to be copying values --> values? If so, how will you identify which values are to be copied, and which left unchanged?

 

[drop05]

You can write code that takes different actions depending on whether cells contain formulae or values.

Post #3 implies you want to leave the destination cells A2 and A3 unchanged. Do you want to be copying values --> values? If so, how will you identify which values are to be copied, and which left unchanged?

yes a2 and a3 are to not be touched they are cells that will not be messed with just a1.
The target book the cell a1 will be blank, but what id like is in the source workbook if a1 is a formula, then when bringing over to the target book it brings the formula and pastes it into the cell at in the target book, however if the source book selected not a formula and just a value then it brings over the value
so wanting to see if there is a specific way in writing this, like a paste special, i know there is a paste special paste formula and stuff but trying to avoid if using something like paste formula it erroring out if it is not a formula or just not pasting the value if it is not a formula in the cell a1

 

[drop05]

You can write code that takes different actions depending on whether cells contain formulae or values.

Post #3 implies you want to leave the destination cells A2 and A3 unchanged. Do you want to be copying values --> values? If so, how will you identify which values are to be copied, and which left unchanged?

kinda like this
saying in the first run source book has a1 as a formula: = A2+A3

sourcebook.worksheet("sheet 1").Range("A1").Copy
targetbook.worksheet("sheet 1").Range("A1").PasteSpecial Paste:=xlPasteFormulas

I know that this will paste the formula if im not mistaken, and paste the formula = A2+A3 into the cell A1 in the targetworkbook
however say another source book cell a1 is just the value "10"
so would want it to copy that value from the source book and paste it into the target book in cell a1 just 10 since its a value

would pasteFormulas do that as well or only paste formulas? if a1 is not a formula it would just ignore it?

 

[StephenCrump]

If it's just cell A1 that you want to copy, try:

sourcebook.Worksheet("sheet 1").Range("A1").Copy targetbook.Worksheet("sheet 1").Range("A1")

If it's a bigger range, I'm still not clear which values in targetbook get left alone (those with dependents in the .UsedRange?) and which ones get overwritten from sourcebook.

Perhaps you could post some sample layouts for sourcebook, targetbook before the copy/paste, and the intended targetbook after the copy/paste?