SOLVED

Query related update data with the help of VBA code.

Iron Contributor

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 - 

Annotation 2022-06-30 134052.pngAnnotation 2022-06-30 134120.pngAnnotation 2022-06-30 134153.pngAnnotation 2022-06-30 134222.png

 

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

 

Please help...

 

Here is a attached file..

2 Replies
best response confirmed by Excel (Iron Contributor)
Solution

@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
Thank you so much sir...
1 best response

Accepted Solutions
best response confirmed by Excel (Iron Contributor)
Solution

@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

View solution in original post