Next Visible Sheet

[Paulrh78]

Hi,

I am trying to find the next visible sheet with vba and copy a cell reference in that sheet and copy to another sheet. I have a password user form which makes a specific sheet visible to the user, there are also 3 other sheets visible as standard. In the activated sheet I will to copy a cell and paste it's contents into one of the other visible sheets and have had no luck.

Please help

Regards

Paul

 

[Marc L]

Hi !​

​

This forum has no luck as you share nothing : no code neither a workbook attachment !​

​

Use the Index worksheet property to check if the next worksheet is visible …​

​

Or restart from here, see in particular How to get the Best Results :​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[Paulrh78]

Thanks for the reply, not really what I was looking for, please see attached; effectively when I open the work book it will ask me for a password to unload Angmering, once I have done this what I want it to do is select the newly visible sheet call Angmering and copy the cell c2 to the worksheet dashboard link and paste this into cell c2, this will then pull through the data from the Angmering sheet and subsequently update my table/pivot in the sheet called Key Data.

See file for a better understanding.

Regards

Paul

 

[Marc L]

You can use INDIRECT formula without any code …​

​

Selecting is useless, for example ['Dashboard Link'!C2].Value2 = [Angmering!C2].Value2 …​

​

In case of several ranges, better is to use With statement for a sheet and use for each sheet its CodeName property :​

    With Sheet2
        .[C2].Value2 = Sheet3.[C2].Value2
'       other ranges here
    End With