• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help in VB Code to hyperlink hidden sheets when other sheets keep hidden

Ateeb Ali

Member
Dear Sir, can someone help me to shorten following two codes, its manually recorded so I am seeing all flashed on screen which I dont want
Code:
Sub Home1()

For sh = 1 To Sheets.Count
Sheets(sh).Visible = -1
Next sh

Sheets("HOME").Select
Sheets(Array("Pending Invoice", "Invoice Report", "Invoice Data", "Invoice", "DC", _
"DC Record", "DC Report")).Select
Sheets("DC Report").Activate
ActiveWindow.SelectedSheets.Visible = False
Range("A8").Select

End Sub

Sub InvoiceReport()

For sh = 1 To Sheets.Count
Sheets(sh).Visible = -1
Next sh

Sheets("Invoice Report").Select

    Sheets(Array("HOME", "Pending Invoice", "Invoice Data", "Invoice", "DC", "DC Record" _
        , "DC Report")).Select
    Sheets("Pending Invoice").Activate
    ActiveWindow.SelectedSheets.Visible = False
    ActiveSheet.PivotTables("PivotTable4").PivotSelect "'11/18/2019'", _
        xlDataAndLabel, True
    Range("A2").Select

End Sub
 
For the first code how about
CSS:
Sub AteebAli()
    Dim Ws As Worksheet
    Application.ScreenUpdating = False
    For Each Ws In Worksheets
        Select Case Ws.Name
            Case "Pending Invoice", "Invoice Report", "Invoice Data", "Invoice", "DC", "DC Record", "DC Report"
                Ws.Visible = xlSheetHidden
            Case Else
                Ws.Visible = xlSheetVisible
        End Select
    Next Ws
End Sub
 
Thanks this works fine against my code Home1
Can you also advice for below code "InvoiceReport"
Code:
Sub InvoiceReport()

For sh = 1 To Sheets.Count
Sheets(sh).Visible = -1
Next sh

Sheets("Invoice Report").Select

    Sheets(Array("HOME", "Pending Invoice", "Invoice Data", "Invoice", "DC", "DC Record" _
        , "DC Report")).Select
    Sheets("Pending Invoice").Activate
    ActiveWindow.SelectedSheets.Visible = False
    ActiveSheet.PivotTables("PivotTable4").PivotSelect "'11/18/2019'", _
        xlDataAndLabel, True
    Range("A2").Select

End Sub
 
You can simply replace the first part of that code, with the code I provided. Just change the sheet names in the select case statement.
 
Dear Sir
attached example file, I want when click button, it should open that sheet and hide all others

BTW, I have tried this code but not working;
Code:
Sub InvoiceReport()
    Dim Ws As Worksheet
    Application.ScreenUpdating = False
    For Each Ws In Worksheets
        Select Case Ws.Name
            Case "HOME", Pending Invoice", "Invoice Data", "Invoice", "DC", "DC Record", "DC Report"
                Ws.Visible = xlSheetHidden
            Case Else
                Ws.Visible = xlSheetVisible
        End Select
    Next Ws
End Sub
 

Attachments

  • CHANDOO.xlsx
    37.9 KB · Views: 2
Ateeb Ali
Did You upload a correct file (.xlsx)?
Or
Did You paste correct code?

At least, from code Your code, there is missing one " before Pending Invoice" ((( as "HOME" )))
Screenshot 2019-11-20 at 10.17.37.png
 
Dear I have applied this code on my original file which not working, I have uploaded a test file to know vb codes against each sheet and then back to main sheet, can you help here.
with this help, i will apply code on my original file
 
Ateeb Ali
Did You noticed:
At least, from code Your code, there is missing one " before Pending Invoice" ((( as "HOME" ))) ?
Did You try to modify that?

Your file do not have any code!

Your original "flashing" challenge...
>> You should add
Application.ScreenUpdating = False
in the beginning and
Application.ScreenUpdating = True
in the end of Your code.

If You cannot reply questions then do not ask questions?
 
Back
Top