VBA coding

[Aberdham]

Hi there,

I tried to combine sheets with different columns to a new sheet. and ran the code. but it says user-type not defined.

following link to the workbook: https://1drv.ms/x/s!Aqt4VfikFsyKdP__4BbJF1LMxlM

could anyone help me to run a debug?

Thank you in advance !

 

[mumps]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

I just ran the macro with no error. It seems to me that sheet names and workbook names keep changing. If this happens the macros won't work properly. Are you using the last 3 most recent files? There is no sheet or workbook named "Revenue" in any of the files.

 

[Aberdham]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

sorry I was referring to the other files

 

[mumps]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

Would it work for you if the WIP sheets were cleared of all data each time you run the macro? The sheets would then be refreshed with all the appropriate rows including the new entries. Since all the old data was deleted, the newly refreshed sheets would have no duplicate data. I think that your private message inbox is full. If it is, please delete some of the messages otherwise you will not receive any new ones.

 

[Aberdham]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

Clearing the data of the sheet would overwrite the original data I presume? I would only want to have the new entries in the Output Sheets to be updated and transferred.

Thank you

 

[mumps]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

In the 3 sheets, you have data in columns A to K. Which columns would you change each month? Will you ever change the WIP number? Are the Invoice numbers on all 3 sheets unique?

 

[Aberdham]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

I will update the Sheets and Keep you posted in a bit. Thank you

 

[mumps]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

Basically I don't want the code to run records to overwrite the original ones, but to keep them and check if new entries were registered, then have them copied.

I don't quite follow. Are you saying that when you add an entirely new row of data, you want that row copied to the appropriate WIP sheet? Are you also saying that if you make a change to any column in an existing row of data, you also want to copy that row to the appropriate WIP sheet?

 

[Aberdham]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

I want to check if it is already transferred to the 2 master output sheet, then to copy to the respective sheet.
There won't be any change of columns as the structure will remain the same

 

[mumps]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

How is the data transferred to the 2 master output sheets?

 

[Aberdham]

Re: Formula split and update master tables into sub tables(product) - Aggregate/replace

I have managed to do that with a couple of index and match