Change Number in cell based on value from 2 sheets columns A

[abjac]

Hi I Have this below code which works good.

IN Sheet "Pagado" the numbers in column A is always sorted ascending
Below code find the last number in sheet "Pagado"(can be blanks between)
So when i press the button it will take the last number in column A and add 1.
It will display the number in Sheet"Masterinvoice" cell M6

I need this code to be extended.
I need it to search in 2 sheets ( "No Pagado" and "Pagado") both in column A
Again in both sheets the numbers in column A will be sorted ascending.
I need it to find the highest number in these 2 sheets column A.
And then display that number + 1 in Sheet "Masterinvoice" cell M6

In this way i will be sure always to find a new invoice number and always the next number.
I need macro because its part of a bigger project with many macroes.

Please have a look

Attached a sample sheet you can look in.

Thanks in advance

Sincerely
Abjac


The working code for do this in one sheet.

Please Login or Register  to view this content.

[AlphaFrog]

This formula in M6 could do the same.

=MAX('No pagado'!A:A, Pagado!A:A)+1

Or this in VBA

Please Login or Register  to view this content.

[abjac]

HI Alphafrog. Yes i know it can be done like this. But the problem is i need to clear that cell sometimes, and then i will clear the formula also.

Thats why i really need a macro. Some times i will use another code to import the invoice back to the masterinvoice sheet, and in this case i need original invoice number to be displayed there.
So the best solution will be a macro,

Thanks

Sincerely

Abjac

[watersev]

hi abjac, option:

Please Login or Register  to view this content.

[abjac]

Brilliant guys. I checked both and they are both working really great, but i guess they are the same.
But great solution.
So thanks to both of you

Have a nice day

Sincerely
Abjac

[efernandes67]

Hi Abjac,

Replace your code with this one. It finds the Max value in both sheets and then biggest is put in cell M6

Please Login or Register  to view this content.

Regards,
Elio Fernandes

[abjac]

Thanks efernandes67. Checked your code it works also perfect. So great help thanks allot.

SIncerely
Abjac