Forum Discussion

Excel's avatar
Excel
Iron Contributor
Jun 30, 2022
Solved

Query related update data with the help of VBA code.

Hello Everyone, 

 

I have tried the code and it run well. 

But, when i update the data either any sheet(EAST RECORD, WEST RECORD, NORTH RECORD and SOUTH RECORD), then it gives update with repititive header and total in YEARLY REPORT sheet and all sheets as well.

like - 

 

So what should i write in VBA code so, it will update and not repititive?

 

Please help...

 

Here is a attached file..

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jun 30, 2022

    Excel 

    Sub LoopYearlyReport()
    Dim ws As Worksheet
    Dim FirstTime As Boolean
    FirstTime = True
    For Each ws In Worksheets
        Worksheets(ws.Name).Select
        If ws.Name <> "YEARLY REPORT" Then
            If Range("A1").Value <> "Division" Then
                InsertHeaders
                FormatHeaders
                AutomateTotalSUM
            End If
            ' 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").Select
            Selection.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
End Sub
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 30, 2022

    Excel 

    Sub LoopYearlyReport()
    Dim ws As Worksheet
    Dim FirstTime As Boolean
    FirstTime = True
    For Each ws In Worksheets
        Worksheets(ws.Name).Select
        If ws.Name <> "YEARLY REPORT" Then
            If Range("A1").Value <> "Division" Then
                InsertHeaders
                FormatHeaders
                AutomateTotalSUM
            End If
            ' 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").Select
            Selection.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
End Sub

Resources