Finding the first visible worksheet

  • Thread starter Thread starter Brettjg
  • Start date Start date
B

Brettjg

Hello there, I have some simple code below which counts the sheets and
formulas in a given workbook. The problem is that if I have run the macro
from the last worksheet visible it gives me a formula count of 0, but if run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes into the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) & Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub
 
Just test visibility:

Sub Macro1()
For Each sh In ActiveWorkbook.Worksheets
If sh.Visible = True Then
MsgBox (sh.Name)
' do your counting
End If
Next
End Sub
 
When you use the For Each sh In ActiveWorkbook.Worksheets, it starts with
Sheets(1) by default. If you only want visible sheets, then add a statement
like:

If sh.Visible = True Then
'your counting code here
End if

How would you start at the last sheet using a For ...Each...Next statement?
You would have to Use something like:

For i = ActiveWorkbook.Sheets.Count To 1 Step - 1
'Code
Next

And Why would you start at the last sheet and work forward? My curiosity is
up.
 
I believe executing this line...

Sheets(1).Activate

will automatically select the first sheet that is visible.
 
Thanks GS

Gary''s Student said:
Just test visibility:

Sub Macro1()
For Each sh In ActiveWorkbook.Worksheets
If sh.Visible = True Then
MsgBox (sh.Name)
' do your counting
End If
Next
End Sub
Click to expand...
 
Thanks JLGWhiz

JLGWhiz said:
When you use the For Each sh In ActiveWorkbook.Worksheets, it starts with
Sheets(1) by default. If you only want visible sheets, then add a statement
like:

If sh.Visible = True Then
'your counting code here
End if

How would you start at the last sheet using a For ...Each...Next statement?
You would have to Use something like:

For i = ActiveWorkbook.Sheets.Count To 1 Step - 1
'Code
Next

And Why would you start at the last sheet and work forward? My curiosity is
up.
Click to expand...
 
Actually, what was happening was that it was only counting the formulas in
the cells from that sheet that I ran it from, over and over. What fixed it was

If Sheets(sh.Name).Cells(countrow, countcol).HasFormula = True Then

instead of
If Cells(countrow, countcol).HasFormula = True Then
 
By the way, I just looked at your actual code and I think you can do what
you want without so much looping. Give this macro a try...

Sub CountSheetsAndFormulas()
Dim Sh As Worksheet
Dim Formulas
On Error Resume Next
For Each Sh In Worksheets
Formulas = Formulas + Sh.Cells.SpecialCells(xlCellTypeFormulas).Count
Next
MsgBox "SHEETS in this workbook: " & Sheets.Count & vbLf & vbLf & _
"FORMULAS in this workbook: " & Formulas
End Sub
 
Hey Rick, that's great, and soooooo much faster. Thankyou very much for the
extra yards. Brett
 
By the way, I just noticed that I didn't complete the type declaration for
the Formulas (counter) variable. This statement...

Dim Formulas

should have been this instead...

Dim Formulas As Long
 
Yes, I know it works without the "As Long" part, but that is because it
defaults to a Variant... I only use Variants when I have to (in large code
and/or especially in loops, they tend to be slow and memory wasters) and
that variable in the code I posted does not need to be a Variant.
 
OK, I see. I'm still wrapping my head around the finer details of
declarations! Thanks again, you've been a great help.Brett
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

val() is not work in my code 1
Find function in VBA 3
How to hide password? 1
Set ws = ActiveSheet Compile error Can't find project or Library 8
Can't delete sheets 8
append multiple worksheets data into a single multi-dimensional array 4
Help with Countrows functionality within a Macro 5
hide multiple worksheets based on a formula value in one cell 1

Back
Top