[NEW] Macro considering newest/last sheet added

[ozstrik3r69]

Dears good afternoon,

May I kindly ask you if someone knows a line/command to input on my macro, to consider the last/newest sheet added.

Actually, I have created a macro considering the sheet named "Tracking".
What I want is, to use the same macro but using the new sheet named for example "Tracking2" and the same macro to use on the sheet named "Tracking3" and so on.
I do not know if I have to consider the name of the sheet or if excel can understand which sheet was the latest one included.


If someone has a solution, I will be very grateful
My very best regards

[Arkadi]

If you are just looking for the last sheet in terms of the right-most tab at the bottom of the workbook then:

Please Login or Register  to view this content.

represents that worksheet.

[ozstrik3r69]

May I ask you.
Actually my code is start like this :

Please Login or Register  to view this content.

May I start by :

Please Login or Register  to view this content.

And then my code :

Please Login or Register  to view this content.

Or Do i have to change completly the code ?


I was also wondering if, once I have a second tab, is there a way to add a code that will not be consider if only one tab is present.

Example :
I want to add a code comparing the tabs and looking for changes.
if their is a change then I will highlight the cell.

Imagine :
Sheet 1
Cell a1 : 22h30
Cell b1 : London

Sheet 2
Cell a1 : 22h31
Cell b1 : London.

Then I want to highlight the cell a1 (Time changed) on sheet 2 considering the same reference in b1 (london in that case)
If their is no sheet 2, then this code will not be run


Hope you will understand my request.


Waiting for feedback
Regards

[ozstrik3r69]

Folks,

I would like to enclose my file, to help you to understand what I want.
I have created a button to run the macro.

As explained in my message above I want to use the same button (Sheet "index") and get that button understands that, if I have a new sheet then I have to run the macro on the last sheet opened/added.

Hope you will be able to help me
Regards

[JOHN H. DAVIS]

Can this help you get started?

Please Login or Register  to view this content.

[ozstrik3r69]

What I see on that code is the vba will create a new sheet.

What I need first is to run the code on the first sheet named "tracking".
Then by myself I will create a new sheet (named Tracking1 for example) and paste my datas.

Once my datas on the worksheet "Tracking1", I will press the button and automatically he will understand that he needs to run the macro on the sheet "tracking1" and not on "tracking" anymore.

Hope I have been clear

Thank you in advance for your help
Regards

[protonLeah]

To select/activate the last sheet added, put the below at the top of the macro

Please Login or Register  to view this content.

[ozstrik3r69]

THIS IS IT !!!!!!!!! (Sorry for UPPER CASE But I am very happy !)
Thank you for this answer.

May I ask you if you can help me also on the second part of my request

I was also wondering if, once I have a second/third/fourth tab/sheet and so on, is there a way to add a code that will not be consider if only one tab is present.

Example :
I want to add a code comparing the last tab with the penultimate tab, looking for changes.
if their is a change then I will highlight the cell.

Imagine :
Sheet 1
Cell a1 : 22h30
Cell b1 : London

Sheet 2
Cell a1 : 22h31
Cell b1 : London.

Then I want to highlight the cell a1 (Time changed) on sheet 2 considering the same reference in b1 (london in that case)
If their is no sheet 2, then this code will not be run

Hope you will understand my request.


Waiting for feedback
Regards

[JOHN H. DAVIS]

FWIW:

Please Login or Register  to view this content.

[Arkadi]

Something like this?:

Please Login or Register  to view this content.

If you want to loop through the whole sheet, then we need a sample of the contents... or an explanation with more detail... how do we decide the last row? based on last sheet, or penultimate?
If it is to check all rows, then keep in mind all details... are the same cities always in the same row? or do you expect the code to look for the city in column B because they may be in different rows?

[ozstrik3r69]

John and Arkadi good afternoon.

First thank your help.

I wrote in my example city and time but it will be a booking number and a date (format mm/dd/yy hh:mm)
Columns will be always the same. For the booking number "H" and date will be always on the same column "D" and column "C".

As i mentioned I need to compare these column from one sheet to another always considering at least the same booking number (Vlookup ?)

Report will never have the same quantity of line, since it will be update every day.
However, we will found the same bookings from one to another sheet

Find enclosed my files

Hope it will help you
Regards

[Arkadi]

Yes the file will be useful, and we are happy to help. But please keep in mind if you give a simple example, you will get a simple example of a solution. That is ok, so long as that is all you need. If you want a detailed solution, which we are happy to work on, then please give all the details from the start.

[ozstrik3r69]

Arkadi good morning.

You right.
Actually what I am missing in vba is this last part.

I want to compare, when a new sheet is added, the two last one (Tracking1 comparing with Tracking2 // Tracking2 comparing with Tracking3 and so on) to check if something is different.
The criteria that I need to check are always depending on the booking Number (column K)

It means :
Booking number is aaaaaaa123 on Tracking1 and Tracking2.

* I need to check if first port is different or the same on column A, on both sheets
* I need to check if second port is different or the same on column B, on both sheets
* I need to check if date is different or the same on column C, on both sheets
* I need to check if date is different or the same on column D, on both sheets
* I need to check if Vessel Name is different or the same on column I, on both sheets

In case one of this items is different I need to highlight it (Yellow for example)

Basically what I need is always to check the two last sheets and highlight when something is different from one to another
Important : In case of highlighting a cell, it should always be on the second sheet (the most actual)

Hope my way to ask in understandable.
Hope you will be able to help me.

Waiting for your feedback
In advance, Thank you !!!
My very best regards

[Arkadi]

I see that you have the same booking number in more than one row...
So for example... row 2 of your Tracking1, how will we know which row in Tracking to compare it to? I assume we don't just compare row 2 to row 2 , 3 to 3, etc.... we have to search for correct match?

Thanks for the great explanation so far

[ozstrik3r69]

Morning Arkadi,

Thinking about it, We will have to add a criteria.
It means :

* Considering Booking Number in Column K AND Container Number on Column AD.

I am trying to think about a way to choose the right Journey Leg.
Because If for example we have a booking, with 2 containers and 2 journey legs, we will have :

* First two lines for the first journey legs
* Two second lines with same containers numbers and same booking number BUT for the second journey legs

IMPORTANT : We can have more than 5 journey legs different for the same booking and up to 10 / 20 containers numbers for one booking

Maybe I will have to add a column with a number at the very beginning to help to choose the right Journey legs (it is an idea...)

If you know a way to "untangle the knot" I will be grateful

Regards

[Arkadi]

Container number is AC not AD? it is also blank in the sample...

I assume "journey leg" means from col A to col B?

If that is true, then the same container/booking number combination has 2 journey legs... which still means that if I compare a booking number/container, to check for changes in Col A or B, it will look like a change because for sure there will be a different col A and B for that item, even though in reality, the first leg and second leg may actually not have changed from Tracking to Tracking1

Yes maybe a number for each leg is a good idea... to compare items, we do need to find a way to make the records unique.

[ozstrik3r69]

Container number is AC not AD? it is also blank in the sample...

Let me updload a new sheet with container number.
Sorry the first sample uplodaded was with blank cell. I thought it was not useful...

I assume "journey leg" means from col A to col B?

It is right.
We are considering a journey leg with all that information. Port of load // ETD // Port of discharge // ETA (From Column A to D)

If we are using a number, it will be better to place it at the very beginning of the sheet or no matter and we can place it at the end of the column ?
I placedit on the last column for the moment.

It means that now, we can consider :
* Booking Number + Container Number + Number

Thank you Arkadi to take that time with me.
Regards

[Arkadi]

Which column you choose for the number does not matter at all, beginning, end, middle, wherever it is we can use it.
I will have a look at the file in a while, a bit busy right now.

[Arkadi]

Someone like jindon would probably have a fancier way of doing this, but since our match criteria is based on 3 different columns, this was the best I could think of... it is not super fast, but I hope manageable.... see if it works for you:

Please Login or Register  to view this content.

[ozstrik3r69]

Forget about it.
I understood !

Sorry

=====================================
Arnaki good afternoon,

May I ask you whats is AZ2 => ws.Range("AZ2")
Or K2 and so on.

I try to understand the code, but I block on this matter.

Waiting for your feedback
Regards
======================================

[Arkadi]

Great
Just to confirm.... that code was placing a formula in AZ2, and then copying that down, to obtain a unique value that is the combination of the fields we use to identify the row for matching.
If the code works and your questions are solved, then please remember to mark the thread as solved? Thanks!

Have a great day.

[ozstrik3r69]

Arnaki good afternoon

First, THANK YOU, it works but...

*I had to change the code (I did it and it is not a big deal)
from :ws.Range("AZ2").Formula = "=K2&""^""&AC2&""^""&AF2"
to : ws.Range("AZ2").Formula = "=RC[-41]&""^""&RC[-23]&""^""&RC[-20]"

Is there a way for my excel to use the real reference like k2 AZ2 and so on ?

* If I have ONLY 3 sheets (including INDEX) code works perfectly but once I have a fourth one and a fifth one and so on, code is not working anymore.
It is like if the code is not able to consider more than 3 sheets

Do you have any ideas ?

Regards and once again THANK YOU !!!!

[Arkadi]

when you say "not working anymore" what does that mean? using wrong sheets? doing nothing? etc...

As for formulas... I don't know the menus for mac office very well, but if it is like windows, then go to file, options, select formulas from menu, and there is a section called "working with formulas". One option is R1C1 Reference Style... un-check the box next to that and it should work with A1 instead of R1C1 style formulas.

Can you send a book with more sheets that is not working for you?

[ozstrik3r69]

Arnaki,

First, I really hope that I do not bother you....

I found the reason why...
If the sheet is protected by password, I need to unprotect the sheet and then I will be able to use the code to compare.

I will find a way to unprotect the sheet at the beginning of the code.

Then I was wondering if I can compare one more item.
On the column H, I have a number, result of the difference between 2 other numbers (Range("H6") // =E6-G6 FOR EXAMPLE)
I was wondering if you can add a line on the code to compare this number from one sheet to another.....

EDIT:
If ws2.Range("H" & i).Value <> ws.Range("H" & myMatch.Row).Value Then ws2.Range("H" & i).Interior.Color = vbYellow

This is what I tried but it seems that the fact to use a formula inside the cell is not helping the code to recognize the value !




If yes and once again if it is not bother you, can you please help me ?

Regards

[Arkadi]

After Set ws = ..... and Set ws2 = ... add this:

Please Login or Register  to view this content.

and at the end of the code

Please Login or Register  to view this content.

[Arkadi]

For comparing col H, it is the same as all the others..... so after (or before)

Please Login or Register  to view this content.

just add:

Please Login or Register  to view this content.

PS... I don't really mind but you keep calling me Arnaki? It is Arkadi... lol

[ozstrik3r69]

For comparing col H, it is the same as all the others..... so after (or before)

Please Login or Register  to view this content.

just add:

Please Login or Register  to view this content.

PS... I don't really mind but you keep calling me Arnaki?

This is what I tried but it seems that the fact to use a formula inside the cell is not helping the code to recognize the value !

Arnaki instead of Arkadi, OMG, I was thinking too much in Star Wars and Anakin Skywalker..... SORRY !!!

[Arkadi]

does not make sense.... .Value should recognize the result of the formula... Your previous sample sheet did not have formulas in H... can you send me a sample with column H formulas included so I can test? Sorry, my last answer was exactly what you tried.. I didn't read your post well enough. But it *should* work.

[ozstrik3r69]

Arkadi

Find enclosed.
I paste the database before using the macro on sheet13 and make somes changes.
You just need to press the button on the Index

Please let me know
Regards

[Arkadi]

Hmmm.... H is empty on these sheets???

[Arkadi]

OOOOH... it is only on the black rows.... but the black rows are being skipped by my program....

However... there is no way to match those black rows because there is no unique value to connect them between 2 sheets?

[ozstrik3r69]

Hmmm.... H is empty on these sheets???

If you run the macro, H6 should be showing an icon set with a number.

See screenshot below.

Column H.png

[Arkadi]

Yes, I posted again after I noticed that.... the code does not compare black lines because they have no container number etc, so how would the code know which black row from each sheet belongs together?
Unless the sheets always have same number of rows... but I assumed that can change?

[Arkadi]

If rows are always the same then I misunderstood... and the project would have been much easier if we can always compare row 2 to row 2, 3 to 3, etc

[ozstrik3r69]

Yes, I posted again after I noticed that.... the code does not compare black lines because they have no container number etc, so how would the code know which black row from each sheet belongs together?
Unless the sheets always have same number of rows... but I assumed that can change?

Saw your answer after a few minutes.
Well actually report will change constantly. means that we will not have the same quantity of line everytime.

I am thinking on a way that I can change this.
The black row has been created to separate each booking.

What I can try to do is.

Letīs take an example with the first booking COP0166855B

We have four lines for this booking because two legs and two containers.
We can try to get macro understand that it is needed to merge four cells on column E and write the formula that I have actually on cell E6. (=IF(D6="",Q5-N5,D6))

Once it is done, I can do the same on the column H, merging four lines and using formula that I have actually on cell H6 (=E6-G6)

Then, for the formula on H, I will have to merge cells on Column G, to inform manually a number.
This number will be useful for the formula on H

Do you think that it is possible ? or too complicated ?

Regards

[Arkadi]

Yes I think even if you merge the 4 cells in H, I can use that to compare the line after the merged cells... for example, merge H2 to H5 (can still be blank, no formula needed), you can leave formula in H6... I can then make the comparing code find the first row AFTER the merged area, and compare those.

[ozstrik3r69]

Yes I think even if you merge the 4 cells in H, I can use that to compare the line after the merged cells... for example, merge H2 to H5 (can still be blank, no formula needed), you can leave formula in H6... I can then make the comparing code find the first row AFTER the merged area, and compare those.

Let me find a way to do that, I revert soon

[Arkadi]

It will be a bit slower, but I think the other option is this:
Every time we do a comparison on rows... find the first cell in H that is not blank, and compare that....
Let me try that before you change your report...

[ozstrik3r69]

It will be a bit slower, but I think the other option is this:
Every time we do a comparison on rows... find the first cell in H that is not blank, and compare that....
Let me try that before you change your report...

Could be an option too
As you said, what I tried have been to merge blank cells.

What I noticed is that it took time to accomplished the task.
I imagine if I run this macro on a computer with a lower configuration...
Not the best option but if needed....

Wait for your feedback
Regards

[ozstrik3r69]

Arkadi,
Timeīs up for me.
I will be with an internet access restricted untill monday morning.

But I will be able at least to see sometimes your answers.

I would like to thank you for your help, once again, because what you did was AWESOME !

I see you soon
Regards

[ozstrik3r69]

Good morning Arkadi,
Hope you have a great week end.
I come back on this post to ask you if you have time to try the solution you wrote below.

Every time we do a comparison on rows... find the first cell in H that is not blank, and compare that....

I was wondering if we are doing more or less the same with another column if itīs not going to be easier.
For example, to check on column D last cell not blank and through a offset to check the result on column H...

I do not know if it can be a solution...or not.

Waiting for your feedback
Regards

[Arkadi]

Sorry, I have been quite busy... I will reply sometime today.

[ozstrik3r69]

Sorry, I have been quite busy... I will reply sometime today.

Do not worry
Far from me the idea to ask you to do the things quickly.

Regards

[ozstrik3r69]

Arkadi,

I am still looking for a solution and I was wondering if :
Using a VBA that copy the line above to copy the last data, we could use formula in "AZ" and then X-Check to be able to compare

Example, If I use without VBA,
* I select the range that I want > Find and Select > Go To Special > Blank > Formula > Copy Range > Special Paste > Value

If I use VBA (but I need to find a solution on how to do it directly by line and not by range...

Please Login or Register  to view this content.

Once I have a line where I have all the data, we can normally compare.
I do not know If I will need all data on the line. I do not know if one cell is blank VBA going to hightlight it ?!

What do you think about it ?

Waiting for your feedback
Regards

[ozstrik3r69]

Arkadi good morning.

I am still trying stuffs to complete my report and last night I was thinking about something.
First, I saw that once I run the report, comparing two sheets, when I save the file, it is about a 4 MO or more.
I was wondering if the fact that the macro is comparing so many columns can be "against" me and do the file heavy.

If, yes, I was wondering if it could not be better to :
* Find a VBA to copy the cell above if cell is blank (see my comment from yesterday.)
* Compare only one criteria. Means the quantity of days on column "E" and column "H"

Maybe comparing only this two criteria could be easier, faster and lighter.

Hope you will be able to help me
Regards

[Arkadi]

I can't reproduce your problem with size... when I save after running the code, it is only a bit bigger...

For H comparison I used:

Please Login or Register  to view this content.

[ozstrik3r69]

Arkadi good morning,

I really want to thank you for your help.
It works fine (at least on my computer..)

For the help you gave me during these 3 days, THANK YOU VERY MUCH !

In case needed, I will contact you again. (Hope no ! :D)

Once again, Thanks
Regards

[Arkadi]

Good morning ozstrik, I'm glad I could help you out
Thanks for marking the thread as solved.