Active Cell Address – Inactive Sheet

[MrDB4Excel]

How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current, active sheet.
This is done to always show the user if a specific cell is actually the active cell in a specific inactive worksheet.
In addition, to make this seem a bit more complicated, my workbook contains 6 sheets, labeled as follows:
Current Rates, USD2CZK Log, EUR2CZK Log, CZK2EUR Log, RUB2EUR Log, EUR2RUB Log.
I want to use VBA to get the cell address of the active cell in each of the sheets USD2CZK Log, EUR2CZK Log, CZK2EUR Log, RUB2EUR Log, EUR2RUB Log and place those cell references, 5 in all, on sheet Current Rates. (Incidentally, these active cell addresses are empty, no data is present)
In addition, the indicated cell addresses of the active cells in those 5 sheets are directly below, in the same column of those 5 sheets, the last entered data. So for example VBA’s results indicate the active cell in each of those 5 sheets is B678. I need to also have VBA return the cell address of the cell above the active cell wherein the last data was entered, in this example, B677.
I assume the VBA involved would need an entry for each of the 5 sheets.
I hope this is an easy fix, any help is greatly appreciated. I have scoured several web pages to find an answer and do not seem to find anything that remotely applies, close, but no cigar.

 

[Yongle]

duplicated post deleted

 

[MrDB4Excel]

Thanks Yongle, works great

 

[MrDB4Excel]

Thanks @jasonb75


Quite the opposite.
- The macro works across the whole workbook and is a replacement for the one provided in my previous post. Apologies for not making that clear

EXPLANATION
the macro is triggered by selection change in the active sheet
VBA is asking ...
Which cell is active?
Which sheet is active?
If it is USD2CZK Log then write value to D10 in sheet Current Rates etc

Many Thanks Yongle,
This is awesome, works beautifully.
How do I give a thumbs up to you?
On another note have you seen my recent post titled?
I see there has been no attempts by anyone to answer or at least reply.
Conditional Formatting Based on a Specific Month Found Within a Range

 

[jasonb75]

Try posting small test examples with XL2BB (button on reply box toolbar) rather than large screen captures that people can't work with.
Many people (myself included) are less likely to respond to threads where they have to retype a load of data to test a suggestion. If I can see what is needed without the data then I will post an untested suggestion, but I rarely spend time on more complex answers for such posts.

 

[MrDB4Excel]

Try posting small test examples with XL2BB (button on reply box toolbar) rather than large screen captures that people can't work with.
Many people (myself included) are less likely to respond to threads where they have to retype a load of data to test a suggestion. If I can see what is needed without the data then I will post an untested suggestion, but I rarely spend time on more complex answers for such posts.

I did not know about using this XL2BB. Will give it a try, thanks, Jason.

 

[MrDB4Excel]

Try posting small test examples with XL2BB (button on reply box toolbar) rather than large screen captures that people can't work with.
Many people (myself included) are less likely to respond to threads where they have to retype a load of data to test a suggestion. If I can see what is needed without the data then I will post an untested suggestion, but I rarely spend time on more complex answers for such posts.

Can I delete this original post: Conditional Formatting Based on a Specific Month Found Within a Range and start over?
If I cannot delete can someone who is in admin?

 

[jasonb75]

You can try clicking the 'Report' button on the first post of that thread and asking admin to delete it.
If you do, I would strongly advise waiting until it has been done before posting a new one in order to avoid falling foul of the duplicate thread rules.

 

[MrDB4Excel]

You can try clicking the 'Report' button on the first post of that thread and asking admin to delete it.
If you do, I would strongly advise waiting until it has been done before posting a new one in order to avoid falling foul of the duplicate thread rules.

Well, I am either blind in one eye or can't see out the other because I do not see a 'Report' button.

 

[jasonb75]

Scroll down a bit, it's bottom left corner of every post (directly opposite the 'Reply' link).

Also, you asked about giving @Yongle a 'thumbs up' earlier, that is in the bottom right corner of each post (except on your own posts) next to the reply link.

 

[MrDB4Excel]

Scroll down a bit, it's bottom left corner of every post (directly opposite the 'Reply' link).

Also, you asked about giving @Yongle a 'thumbs up' earlier, that is in the bottom right corner of each post (except on your own posts) next to the reply link.

Thanks Jason