Copy cell data if cell blank!

[Spellbound]

Hi all

I have another query that I believe can only be solved using VBA.

In a row of input data, some cells are duplicated from previous cells in the same row but in some instances, the user needs to be able to input different information.

e.g. M4=F4 if left blank and the same would apply to columns N=G, O=H, P=I & Q=J.

I have done some searching but nothing quite matches my requirements and recording a macro only produces the following result.

Please Login or Register  to view this content.

The data to be copied could be either text or a number and the formatting needs to remain the same as the copied cell.

Some of the copied cells may be blank and should also be blank in the destination cells.

TIA ...spellbound

[royUK]

maybe

Please Login or Register  to view this content.

[Spellbound]

Hi Roy

I think that I may have confused the issue a bit.

Rather than run a macro, the code needs to run automatically as the user inputs the data.

They will be inputting a row of data from column A onwards. So as the data is entered into G, H, I & J, the same data is automatically entered into the corresponding columns M, N, O, P & Q of the same row.

However, there are times when the information in M, N, O, P & Q is going to be different and therefore need to be overwritten with the users manual input.

Thanks again ...spellbound

[royUK]

This would need a WorkSheet_Change event, the workbook should contain the same structure and some dummy data of the same attach a workbook with cells marked

[Spellbound]

Hi Roy

Had a dummy workbook to hand, and I have highlighted 2 examples of where the data would be different.

Hope this helps to make more sense.

spellbound

[royUK]

Try this

Please Login or Register  to view this content.

To add it to your worksheet, do the following:

Copy the code
Select the worksheet in which you want the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

[Spellbound]

Roy, thanks for that quick response.

I have tried out the code (with revised columns) in an updated test workbook and it works as requested.

However, I have stumbled upon a couple of problems which will affect data.

If any of the source cells are amended or changed, the data in the target columns is not updated and even forcing a calculation or save does not make them change.

Likewise if the manual input in the target columns also needs to be changed, then these columns have to be manually overwritten again. I tried removing the data leaving them blank but that did now work either.

Any suggestions?

[royUK]

If a value is amended in the input columns this code will upfdate the other cells with the new value

Please Login or Register  to view this content.

[Spellbound]

Hi Roy

Thanks for that last bit of code. It works fine where the data in the target columns match the data in the source columns.

However, could the code be made to handle the following situation.

If the manually entered data in the target columns is deleted leaving blank cells; can the target columns then be updated with the source data if left blank but still allow for user data to be manually entered into the target columns if required.

TIA ...spellbound

[Spellbound]

<<< bump >>>

[Spellbound]

<<< bump >>> Roy, any ideas, not been able to solve this yet!