Copying Data without overwriting (edit macro)

[jimmy_nora]

I have the following macro that copies data from sheet1 and pastes it to sheet2.

I want to edit this macro to not overwrite the data in sheet2 when pasting in the data from sheet1. it should only paste in the data if the respective cell is empty. if something is already there it should just move on to the next cell.

Previously what this macro did was it saved the old data as a comment in the respective cell (sheet2) and pasted in the data from sheet1.

JN

Please Login or Register  to view this content.

posting here too:
http://www.mrexcel.com/forum/showthread.php?t=532593

[watersev]

hi, jimmy_nora, I have a question for you. So the code finds matching value in Sheet2, obviously the value is not empty, we move one row down, cell has value as well, move down again .... So what's the point to use search for value then? Or you have the data in groups and that will anyway find empty cell within required group?

[jimmy_nora]

Let me explain a bit detail how the macro works.
- We compare every value in sheet1 column C with sheet2 coloumn A to find a match

If there is a match then do this in that respective row of the matching value in sheet2:
- copy the data from the respective columns in sheet1 (D,F,H) and paste it in the sheet2 columns F, D, I respectively.

Now what I want is that when the value are to be pasted in the columns of sheet2 they should not overwrite the already entered values in the respective cell. it should just move on.

I hope this clears your question.

[watersev]

it's always better to have sample of the workbook, though you can test it by yourself:

Please Login or Register  to view this content.

[jimmy_nora]

here is the sample file

in my real project there are a lot of columns (about 30) between both sheets that I have to copy between. can you point out where I need to modify the code to accommodate the additional columns ?

[watersev]

could you please test the code "CopyData1"

[jimmy_nora]

Yes for this case the macro works.
what changes do i need to make (apart from these on the top of the code NbCol , ColSRC , ColDEST ) to copy the data between the following 16 columns from sheet 1 to sheet 2

Sheet1 : C,A, B, N, M,E,H,F,G,J,K,L, I, Q, D, O
Sheet2: D,O, Q, V, AF, AG, AH, AM, AN, AR, AS, AT, AY, BA, BK, BL

And also we are now comparing all the values in Sheet1 Column P (row 2 and onwards) with Sheet2 Column A (row 5 and onwards) to find a match before copying the data over.


Can you please edit your code to accommodate these two changes .

[jimmy_nora]

i have uploaded an updated version of the file to show what I mean. please test your macro on this when you make changes.

[watersev]

ok, I'll have a look. As far as I see it's a completely separate question, right?

[watersev]

please check attachment, run code "CopyData1"

[romperstomper]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this.

Note: this means posting links to ALL your cross-posts.

[jimmy_nora]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this.

Note: this means posting links to ALL your cross-posts.

I did provide a link to a cross post. It is in the OP

[jimmy_nora]

@Watersev

Thank you very much. Your code works like a beauty. Still checking my results though. So far so good.

[romperstomper]

I said ALL your cross posts. You did not link to this one at OzGrid, for example. (there may be others for all I know)

[jimmy_nora]

Please can you re-check the macro-code because it is overwriting the old data even when it should not ?
It is only supposed to fill in the empty cells and skip the cells with data.

[watersev]

hi, jimmy_nora, please check attachment