Visible Property

Question

How do I know What is the name of Visible Worksheet in a workbook using VBa code? or something else.

How to know which is the name of second sheet which is visible and it is second in number from left out of say total 5 worksheets in workbook. first is 1 and second is AB. and so on. how to get the name of worksheet which is active and second number

Answer 1

hope you got my point here.

Answer 2

You did not get my query correctly now.

Above code mentioned by me is working correctly now but below line is making my life difficult actually

 Sheets.Add After:=Sheets(Sheets.Count)

Above code adds another sheet name every time this macro runs like sheet9 and sheet 10 and so on .
I do not want to add new sheet every time here.

I want to get the name of active sheets in given workbook in Worksheet named as Sheet 9 here.

How to do it now?

Thanks.

Answer 3

In a regular module, if you refer to CELLS or RANGE means you refer to the ActiveSheet in the ActiveWorkbook

Sub Test()

Range("A1") = 1
End Sub

If you use a WORKSHEET object to refer to the RANGE means you refer to the sheet in the ActiveWorkbook

Sub Test()  

  Worksheets("MySheet").Range("A1") = 1

End Sub

If you want to refer to more then one RANGE use WITH

Sub Test()
With Worksheets("MySheet")
.Range("A1") = 1
.Range("A2") = 2
End With
End Sub

If you use a WORKBOOK object to refer to a WORKSHEET that refers to a RANGE means you refer to the exact location.

Sub Test()
With Workbooks("MyFile.xlsm").Worksheets("MySheet")
.Range("A1") = 1
.Range("A2") = 2
End With
End Sub

There is a reserved word THISWORKBOOK in Excel VBA, which refers to the workbook which contains the code.

Sub Test()
With ThisWorkbook.Worksheets("MySheet")
.Range("A1") = 1
.Range("A2") = 2
End With
End Sub

That's how VBA works. Make 2 new files, save both, then try each of the codes above to refer to different cells in the active and the other file. It is essential to examine this in detail to understand how it works.

Andreas.

Answer 4

Hi,

Sub NameSheets()
'Updateby Extendoffice
    Dim x As Long, y As Long, z As Long
    Dim ws As Worksheet
    Dim shtCnt As Integer
    x = 1
    y = 1
    z = 1
    shtCnt = ThisWorkbook.Sheets.Count
    On Error Resume Next
    Application.ScreenUpdating = False
    Sheets.Add After:=Sheets(Sheets.Count)
    For i = 1 To shtCnt
        If Sheets(i).Visible = xlSheetHidden Then
            Cells(x, 2) = Sheets(i).Name
            x = x + 1
        End If
        If Sheets(i).Visible = xlSheetVisible Then
           Cells(y, 1) = Sheets(i).Name
            y = y + 1
        End If
        If Sheets(i).Visible = xlSheetVeryHidden Then
            Cells(z, 3) = Sheets(i).Name
            z = z + 1
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

I am using above code, but it is working fine but it is adding new sheet after existing sheet and then doing the calculation.

How to avoid that actually?

Please ket  knwo if i can specify existing sheet in which it can do the calcualtion ?

Answer 5

Sub Test()
Dim OtherFile As Workbook

Set OtherFile = Workbooks("Whatever.xlsx")
With OtherFile.ActiveSheet
MsgBox .Name & " is the " & .Index & " sheet"
End With
End Sub