hide / unhide and give hyper link to open particular sheet for each sheet from summary sheet

ExcelSheetsData.gif
Please see the image
VBA need to check the summary sheet .
In summary_sheet and  column A has sheet name for all sheets
In summary_sheet and  column C has  row count for all sheets

VBA need to do two things
1-Hide sheet if row count is zero .
2-Give hyper link on column A's each cell  so user can go to each sheet directly by clicking on it .
 
for example some thing like this .

  For Each ws In ActiveWorkbook.Sheets
 
  Next ws
LVL 3
atherhAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
An example workbook would be more useful than images. Are you trying to create a Table of Contents?
atherhAuthor Commented:
Yes , It is sort of Table of Content .

I dont know how to attach the excel work book. :)
Roy CoxGroup Finance ManagerCommented:
There should be a some blue text at the bottom right of the new reply box that says attach file. Click it and follow the instructions
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

atherhAuthor Commented:
Ok . I am attaching sample excel sheet.
VBA_work_Sample.xlsx
atherhAuthor Commented:
Ok . I have done the second bit which is go to particular sheet with 'Back to INDEX' hyper link in last column of that sheet .

Now i have to do first bit .which is

1-Hide sheet if row count is zero .



Thanks,
atherhAuthor Commented:
Ok . I have done the second bit which is go to particular sheet with 'Back to INDEX' hyper link in last column of that sheet .

Now i have to do first bit .which is

1-Hide sheet if row count is zero .
VBA_work_Sample.xlsm
atherhAuthor Commented:
Ok , there was a mistake in logic .

I did not want to scroll all sheet to make table of content as
I already have table of content and I just wanted to make a link to that sheet and bank to SUMMARY SHEET INDEX link on each sheet.

If you can make more clean and less code it will be great else i am happy for second part .

any way I have corrected it and attached it but still hidden issue is remaining by checking count of summary sheet index column c .

Please help !

thanks,
Ather
VBA_work_Sample.xlsm
Roy CoxGroup Finance ManagerCommented:
I think this does what you want
Option Explicit

Sub IndexIt()
    Dim Ws As Worksheet, WsInd As Worksheet, lStartRow%, lStartCol, sBackRange As String
    Dim lastColumn As Integer

    Set WsInd = Sheets("Summary_Sheet")

    lStartRow = 2
    lStartCol = 1

    sBackRange = "A" & lastColumn
    'Add the links
    For Each Ws In Worksheets
        With WsInd
            If Ws.Name <> .Name Then
                .Cells(lStartRow, 3).Value = Ws.Range("A1").CurrentRegion.Rows.Count
                ''///hide sheet if rows count is zero
                If .Cells(lStartRow, 3).Value = 0 Then Ws.Visible = xlSheetHidden
                .Hyperlinks.Add .Cells(lStartRow, lStartCol), "", "'" & Ws.Name & "'!A1"
                .Cells(lStartRow, lStartCol).Value = Ws.Name
                ''/// you can add a sheet description with the line below
'                .Cells(lStartRow, 2).Value =
                lStartRow = lStartRow + 1
                '##2: Add link back to index,
                lastColumn = Ws.Range("A1").CurrentRegion.Columns.Count + 1
                Ws.Hyperlinks.Add Ws.Cells(1, lastColumn), "", "'" & .Name & "'" & "!A1"
                Ws.Cells(1, lastColumn).Value = "Back to Index"
            End If
        End With
    Next Ws

    WsInd.Activate
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
atherhAuthor Commented:
Option Explicit

Sub IndexIt()
    Dim Ws As Worksheet, WsInd As Worksheet, lStartRow%, lStartCol, sBackRange As String
    Dim lastColumn As Integer

    Set WsInd = Sheets("Summary_Sheet")

    lStartRow = 2
    lStartCol = 1

    sBackRange = "A" & lastColumn
    'Add the links
    For Each Ws In Worksheets
        With WsInd
            If Ws.Name <> .Name Then
                'As i already have table of content and i dont need new one so i hide it .
                '.Cells(lStartRow, 3).Value = Ws.Range("A1").CurrentRegion.Rows.Count
                ''///hide sheet if rows count is zero
                If .Cells(lStartRow, 3).Value = 0 Then Ws.Visible = xlSheetHidden
                .Hyperlinks.Add .Cells(lStartRow, lStartCol), "", "'" & Ws.Name & "'!A1"
                .Cells(lStartRow, lStartCol).Value = Ws.Name
                ''/// you can add a sheet description with the line below
'                .Cells(lStartRow, 2).Value =
                lStartRow = lStartRow + 1
                '##2: Add link back to index,
                lastColumn = Ws.Range("A1").CurrentRegion.Columns.Count + 1
                Ws.Hyperlinks.Add Ws.Cells(1, lastColumn), "", "'" & .Name & "'" & "!A1"
                Ws.Cells(1, lastColumn).Value = "Back to Index"
            End If
        End With
    Next Ws

    WsInd.Activate
End Sub

Open in new window


Only one line change and it work.
really appreciate your help.

Thanks,
Roy CoxGroup Finance ManagerCommented:
Im glad it worked for you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.