Forum Discussion

Iron Contributor

Mar 17, 2021

Question related to add headers and hide sheet

Hello Everyone, i have created VBA, which successfully run as well as when i update data in particular sheet then it will give result in YEARLY REPORT sheet. But i have two problems - 1st probl...

Iron Contributor

Mar 17, 2021

HansVogelaar

Thank you for reply.
But sir when i hide the sheet (WEST SHEET) so it is showing all sheet data.

Like -

So what should i write?

Here I am attach file which i have hide WEST sheet.

Report.xlsm38 KB

HansVogelaar

MVP

Mar 17, 2021

Excel

If you want to exclude hidden sheets:

Public Sub FinalReportLoop()
    Dim i As Integer
    Dim ws As Worksheet
    Dim wsR As Worksheet
    Dim rng As Range
    
    Application.ScreenUpdating = False
    Set wsR = Worksheets("Yearly Report")
    
    wsR.Cells.Clear
        
    For i = 1 To Worksheets.Count - 1
        Set ws = Worksheets(i)
        If ws.Visible = xlSheetVisible Then
            AddHeaders ws
            FormatData ws
            AutoSum ws
        
            If i = 1 Then
                Set rng = wsR.Range("A1")
            Else
                Set rng = wsR.Range("A" & wsR.Rows.Count).End(xlUp).Offset(3)
            End If
        
            ws.UsedRange.Copy Destination:=rng
        End If
    Next i
    
    wsR.Columns("A:F").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

The other procedures remain the same.

Excel

Iron Contributor

Mar 17, 2021

HansVogelaar

Thank you for the Reply😊

I have another doubt that - what if YEARLY REPORT sheet in middle.

Like -

After run this code, it give wrong outcome 2 times EAST and WEST sheet then come NORTH sheet.

Here is a attach file you can see that in YEARLY REPORT sheet.

So what will i add?

Report.xlsm39 KB

HansVogelaar

MVP

Mar 17, 2021

Excel

Public Sub FinalReportLoop()
    Dim ws As Worksheet
    Dim wsR As Worksheet
    Dim rng As Range
    Dim f As Boolean
    
    Application.ScreenUpdating = False
    Set wsR = Worksheets("Yearly Report")
    
    wsR.Cells.Clear
        
    For Each ws In Worksheets
        If ws.Visible = xlSheetVisible And ws.Name <> wsR.Name Then
            AddHeaders ws
            FormatData ws
            AutoSum ws
    
            If f Then
                Set rng = wsR.Range("A" & wsR.Rows.Count).End(xlUp).Offset(3)
            Else
                Set rng = wsR.Range("A1")
                f = True
            End If
            ws.UsedRange.Copy Destination:=rng
        End If
    Next ws
    
    wsR.Columns("A:F").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

Excel
Iron Contributor
Mar 18, 2021
HansVogelaar
Sir how can we do total for JAN, FEB and MAR for all sheet then after run, it should come in YEARLY REPORT sheet.
Like -

And After run this code, it should be update in YEARLY REPORT. So what code will i write?

Please help...???

Here is a attach file.
Report.xlsm38 KB