Copy Cells content to another sheet

Hi there,

I hope you are all well.

I have a workbook with 2 sheets, 1st one named as "In Process" and the other one "Completed".

The "In Process" sheet there's a table with examples of invoices not paid and still in process. The "Complete" is all the invoices already paid.

I have a code that one of you helped me a lot. That code copies automatically on the Worksheet_Change event the paid invoices from "In Process" to "Completed" based on the value of row F which is the status ("paid"; "not paid"). If the user selects "Yes" on from the F column, it will copy to the "Completed" sheet without deleting it from "In Process".

The code is this one:

Please Login or Register  to view this content.

I now need to copy not all row but only some cells. As you can see on the attached example, the order of the headers on "In Process" sheet is not the same as the "Completed" and I need to copy only the common headers.

Let's say. on "In Process" sheet, the header "Date" is within the G column and on "Completed" is on C Column.

I need a peace of code that will copy the common headers to the right place and, if possible, I need the CopyContents as to not mess with the formatting (My original file is formatted with different colors).

If possible, as the Invoices are an unique number, is is possible to avoid duplication? e.g. A user by mistake changes to paid 2 times on the same invoice.

You assistance on this matter will be highly appreciated.

Best regards,
Filipe

Copy Contents if PAID 2.xlsm

Hello Pip,

I have attached your worksheet with a new macro to be added into the Modules, and some changes to the Event Change macro.

The new macro covers the removal of duplicates in the Event Macro. Removal of duplicates also removes all the rows down to 25, so the "ReplaceBorders" macro restores the formatting.

The Event Macro has been changed to copy the values to the appropriate column, one by one.

Hope this helps.

David


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David A Coop,

Thank a million for your assistance.

On the example I have attached it worked perfectly but not on my actual file.

I've made some changes such as sheet names and so on but I am still getting an error on this line:

Please Login or Register  to view this content.

It says that Variable was not defined.

This is strange as on the example it works perfectly.

Regards,
Filipe

Hi Filipe,

You probably have Option Explicit set at the top of your live macros.

Just include a Dim r statement in your macro. That should fix the problem,

David

Hi David,

Thanks a million for your reply.

I did have Option Explicit at the beginning of the macros but the "Dim r" solved the issue. Really appreciated.

Now the only problem is the formatting. The target table format changes. Is it possible to have something on the code such as PastValues only?

Sorry for this as you have already helped me a lot.

Best regards,
Filipe

Hi Filipe,

I think you must have missed the code I added to your worksheet.

In the file I sent you, have a look at Module1! There you will find the code that fixes the formatting.

The code below needs to be in Module1.

Regards,

David

Please Login or Register  to view this content.

When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David,

Thank you for your reply.

Yes, I do have the above code on Module 1 but when running the Worksheet_Change event I got an error on the Call ReplaceBorders as following:

"Expected variable or procedure, not module".

I've tried to copy the Module code into the Worksheet_Change but nothing happens.

I've attached my file to this thread. On my file the Sheet "Customer Nomination" is the "In Process" as per the example, and the "Customer Tracking" the "Completed".

The criteria to copy from one side to the other is the "Yes" and it's located on the column AD (Outlined in Yellow).

Many thanks in advance and kind regards,
Filipe

Hi Xladept,

Thank you for your reply.

To be honest, I tried your code but could not understand it to use on my file.

I am just a beginner in VBA. The part I did not understood was the "Target.Offset" criteria (e.g -4).

If your code only paste the values, that will be exactly what I need.

Not sure how to change your code to my actual file. Can I ask your help on it please?

Many thanks in advance and kind regards,
Filipe.

Thanks a million xladept.

This example worked as expected on the example file.

I will try on my actual file and will let you know if that worked fine.

Thanks for everything, really appreciated as well for the explanation of your code. Learned something valuable today.

Have a great day.

Best regards,
Filipe

Hey xladept,

Thanks again.

This code works as well on my actual file but I had to take two lines of you code out. The delete row one and the insert row one.

By any chance, is there a way to check for duplicates? I mean, if a line is a duplicate (on this example the invoice number as should be unique) the code will not copy the contents or delete any duplicates?

Is is too complicate to create a line for that? And as well for the insert line, instead of deleting the row, only delete row contents.

Should I create a new thread for this one?

Thanks a million in advance.

Best regards,
Filipe.

Thanks for your prompt reply.

I got an error here:

Please Login or Register  to view this content.

It says:
"Compile error: Invalid or unqualified reference"

Is that due to the previous line is an "IF" statement as well? On this case you have two "IF's" and not end "IF" between them.

Thanks a million.

Best regards,
Filipe.

Hi xladept,

I knew it was something easy and straight forward...

Thanks a million one more time. You support was outstanding.

Best regards,
Filipe