I THINK what you're saying is that you DON'T want to change the order of the tabs.
if you have:
Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6, Sheet7, Sheet8, Sheet9, Sheet10 (and more)
you want them in the same order, but if Sheet6 is selected,
it would be in the leftmost position:
Sheet6, Sheet7, Sheet8, Sheet9, Sheet10, Sheet11, Sheet12, Sheet13
NOT reorder the sheets:
Sheet6, Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet7, Sheet8, Sheet9,
If this is the case, I wrote a quick loop
Sub Update_Sheet_Display()
Dim inx
For inx = 1 To Sheets.Count
Application.ScreenUpdating = False
Sheets(Sheets.Count).Select
If (Sheets(inx).Visible = True) Then
Sheets(inx).Select
Application.ScreenUpdating = True
Debug.Assert False
End If
Next inx
End Sub
Dim inx
For inx = 1 To Sheets.Count
Application.ScreenUpdating = False
Sheets(Sheets.Count).Select
If (Sheets(inx).Visible = True) Then
Sheets(inx).Select
Application.ScreenUpdating = True
Debug.Assert False
End If
Next inx
End Sub
This will work until the last sheet is displayed on the right.
Is this what you're looking for?
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
From: "David Gathmann dmgathmann@yahoo.com [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
Sent: Friday, April 24, 2015 10:14 AM
Subject: [ExcelVBA] Re: Making a sowksheet the leftmost in the tabs
Donaldb36,Thanks for the reply, but that is not what I'm saying. My Worksheets(SPY) .Activate takes care of getting to the proper worksheet after the scrolling. What I'm saying is that there are, say, 40 worksheets and 15 - 20 can be displayed in the tabs at any one time depending on the characters in the worksheet (tab) name. The worksheet that I want to be active after the macro runs happens to have, say, the 10th index so it is the 10th tab from the left since for some reason the leftmost tab is set to index of one by going through the loop examining the worksheets. I would like that 10th tab to be the leftmost tab shown. I hope that this clarifies the problem.Dave
__._,_.___
Posted by: Paul Schreiner <schreiner_paul@att.net>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
.
__,_._,___
Tidak ada komentar:
Posting Komentar