Forum Discussion

Excel's avatar
Excel
Iron Contributor
Jul 13, 2022
Solved

Query related to hide with the help of VBA code

Hello Everyone, 

 

 Whenever i hide the sheet like :

 

 

 

 

then it run the code, then it shows error

like - 

 

 

 

So, what should i write to show data which are visible sheet in YEARLY REPORT sheet?

 

Please help...

 

Here is a attached file...

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jul 13, 2022

    Excel 

    Sub LoopYearlyReport()
    Dim ws As Worksheet
    Dim FirstTime As Boolean
    FirstTime = True
    For Each ws In Worksheets
        If ws.Visible = xlSheetVisible And ws.Name <> "YEARLY REPORT" Then
            ws.Select
            If Range("A1").Value <> "Division" Then
                InsertHeaders
                FormatHeaders
            End If
            AutomateTotalSUM
            ' SELECT CURRENT DATA
            Range("A2").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            ' COPY DATA
            Selection.Copy
            ' SELECT "YEARY REPORT"
            Worksheets("YEARLY REPORT").Select
            ' PASTE DATA
            Range("A30000").End(xlUp).Select
            If FirstTime <> True Then
                ActiveCell.Offset(1, 0).Select
            Else
                FirstTime = False
            End If
            ActiveSheet.Paste
        End If
    ' MOVE TO THE THE NEXT SHEET IN THE LOOP
    Next ws
    Worksheets("Yearly Report").Select
    If Range("A1").Value <> "Division" Then
        InsertHeaders
        FormatHeaders
    End If
    AutomateTotalSUM
    Application.CutCopyMode = False
End Sub

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 13, 2022

    Excel 

    Sub LoopYearlyReport()
    Dim ws As Worksheet
    Dim FirstTime As Boolean
    FirstTime = True
    For Each ws In Worksheets
        If ws.Visible = xlSheetVisible And ws.Name <> "YEARLY REPORT" Then
            ws.Select
            If Range("A1").Value <> "Division" Then
                InsertHeaders
                FormatHeaders
            End If
            AutomateTotalSUM
            ' SELECT CURRENT DATA
            Range("A2").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            ' COPY DATA
            Selection.Copy
            ' SELECT "YEARY REPORT"
            Worksheets("YEARLY REPORT").Select
            ' PASTE DATA
            Range("A30000").End(xlUp).Select
            If FirstTime <> True Then
                ActiveCell.Offset(1, 0).Select
            Else
                FirstTime = False
            End If
            ActiveSheet.Paste
        End If
    ' MOVE TO THE THE NEXT SHEET IN THE LOOP
    Next ws
    Worksheets("Yearly Report").Select
    If Range("A1").Value <> "Division" Then
        InsertHeaders
        FormatHeaders
    End If
    AutomateTotalSUM
    Application.CutCopyMode = False
End Sub

Resources