VBA/Macro - Loop through worksheets

[acerlaptop]

Hi,

I have a code below that I'm currently finishing. This code will update several columns on all worksheets whenever a new data is added. But it doesn't seem to be working.

Hope anyone can help me.

Thanks in Advance

Sub UPDATE_SUMMARY_WB()

Workbooks("Cummulative WHT Monitoring.xlsx").Activate
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

Dim LastRow1 As Integer
Dim LastRow2 As Integer
Dim LastRow3 As Integer
Dim strN1 As String
Dim strN2 As String
Dim strN3 As String

strN1 = "2307 Template (FINAL)"
strN2 = "Cummulative WHT Monitoring"
strN3 = Workbooks("2307 Template (FINAL).xlsm").Sheets("FPAGE").Range("F7").Value
LastRow2 = Workbooks(strN2 & ".xlsx").Worksheets(strN3).Cells(Rows.Count, 1).End(xlUp).Row

'Update value of Total WHT
LastRow1 = Workbooks(strN2 & ".xlsx").Worksheets(strN3).Cells(Rows.Count, 1).End(xlUp).Row
Sheets(strN3).Range("E" & LastRow2 + 1 & ":" & "E" & LastRow1).FormulaR1C1 = _
"=SUMIF('[2307 Template (FINAL).xlsm]DATA'!C12,RC[-4],'[2307 Template (FINAL).xlsm]DATA'!C8)"
Sheets(strN3).Range("E2:E" & LastRow1).Value = Sheets(strN3).Range("E2:E" & LastRow1).Value
Sheets(strN3).Range("E2:E" & LastRow1).NumberFormat = "#,##0.00_);(#,##0.00)"

'Details for Quarter
'Helper
Sheets(strN3).Range("I" & LastRow2 + 1 & ":" & "I" & LastRow1).FormulaR1C1 = _
"=INDEX('[2307 Template (FINAL).xlsm]DATA'!C13,MATCH(RC[-8],'[2307 Template (FINAL).xlsm]DATA'!C12,0))"
'Quarter
Sheets(strN3).Range("D" & LastRow2 + 1 & ":" & "D" & LastRow1).FormulaR1C1 = _
"=ROUNDUP(MONTH(RC[5])/3,0)&""Q"""
Sheets(strN3).Range("D2:D" & LastRow1).Value = Sheets(strN3).Range("D2:D" & LastRow1).Value
Sheets(strN3).Range("D2:D" & LastRow1).NumberFormat = "General"
Sheets(strN3).Range("D2:D" & LastRow1).HorizontalAlignment = xlCenter
Sheets(strN3).Range("I:I").Clear

'Helper for PREPARED Column
Call ADD_HELPER_WS
Call LIST_FILES_W_DATE
LastRow3 = Workbooks(strN2 & ".xlsX").Worksheets("HELPER").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("HELPER").Range("D1:D" & LastRow3).FormulaR1C1 = _
"=LEFT(RC[-3],FIND(""-"",RC[-3])-1)"
Sheets("HELPER").Range("E1:E" & LastRow3).FormulaR1C1 = _
"=MID(RIGHT(RC[-3],3),2,1)&""Q"""
Sheets("HELPER").Range("F1:F" & LastRow3).FormulaR1C1 = _
    "=COUNTIFS(C[-2],RC[-2],C[-4],RC[-4])"

'PREPARED Column
Sheets(strN3).Range("G2").FormulaArray = _
"=MAX(IF(HELPER!C[-3]=RC[-4],IF(HELPER!C[-2]=RC[-3],HELPER!C[-4])))"
Sheets(strN3).Range("G2:G" & LastRow1).FillDown
Sheets(strN3).Range("G2:G" & LastRow1).Value = Sheets(strN3).Range("G2:G" & LastRow1).Value
Sheets(strN3).Range("G2:G" & LastRow1).NumberFormat = "dd-mmm-yyyy"
Sheets(strN3).Range("G2:G" & LastRow1).HorizontalAlignment = xlCenter

'VERSIONS column
Sheets(strN3).Range("F2").FormulaArray = _
"=INDEX(HELPER!C[0],MATCH(1,(HELPER!C[-2]=RC[-3])*(HELPER!C[-1]=RC[-2]),0))"
Sheets(strN3).Range("F2:F" & LastRow1).FillDown
Sheets(strN3).Range("F2:F" & LastRow1).Value = Sheets(strN3).Range("F2:F" & LastRow1).Value
Sheets(strN3).Range("F2:F" & LastRow1).NumberFormat = General
Sheets(strN3).Range("F2:F" & LastRow1).HorizontalAlignment = xlCenter

Call DELETE_HELPER_WS

'Format for Date released
Sheets(strN3).Range("H2:H" & LastRow1).NumberFormat = "dd-mmm-yyyy"
Sheets(strN3).Range("H2:H" & LastRow1).HorizontalAlignment = xlCenter

'Remove Duplicates
Sheets(strN3).Range("A:H").RemoveDuplicates Columns:=1, Header:=xlYes
Application.CutCopyMode = False

'Sort base on Quarter & Name
With ActiveSheet.Sort
.SortFields.Add Key:=Range("C1"), Order:=xlAscending
.SortFields.Add Key:=Range("D1"), Order:=xlAscending
.SetRange Range("A2:H" & LastRow1)
.Header = xlYes
.Apply
End With
Sheets(strN3).Sort.SortFields.Clear

Next ws
End Sub

 

[steve the fish]

What isnt working? The loop you have here isnt doing anything other than making the code repeat itself by how many worksheets you have.

 

[acerlaptop]

What isnt working? The loop you have here isnt doing anything other than making the code repeat itself by how many worksheets you have.

All the task inside the loop. I don't know what's happening but some values does not appear where the selected range should be.

 

[steve the fish]

The tasks inside the loop are happening over and over for no apparent reason as there is no need for a loop here that i can tell. I cant see what these other macros being called do either.