VBA/Macro - Loop through worksheets

acerlaptop

New Member
Joined
Feb 17, 2020
Messages
44
Office Version
  1. 2013
Platform
  1. Windows
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 17, 2020
Messages
44
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,462
Messages
5,624,871
Members
416,063
Latest member
chaulon199

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top