Hi there,
Can anybody assist me with the following? I need to re-develop a macro that enters a formula into each worksheet within a workbook. The workbook has 25 worksheet and I need to insert a formulat that divided the last reading in the columns "C", "G", "E" and "I". Here is what I was able to come with:
Sub Test()
Dim rng As Range
Set rngC = Range("C" & Cells(Rows.Count, "C").End(xlUp).Row)
Set rngE = Range("E" & Cells(Rows.Count, "E").End(xlUp).Row)
Set rngG = Range("G" & Cells(Rows.Count, "G").End(xlUp).Row)
Set rngI = Range("I" & Cells(Rows.Count, "I").End(xlUp).Row)
rngC.Offset(1, 0).Value = rngC / 14.5
rngE.Offset(1, 0).Value = rngE / 14.5
rngG.Offset(1, 0).Value = rngG / 14.5
rngI.Offset(1, 0).Value = rngI / 14.5
End Sub
When I run the code, it work perfectely and inserts the formula into desired destination (divides last value in any of the columns "C", "G", "E" and "I"). But, what I can not do is make code loop so that the macro is applied for the entire workbook and spares me from running the-present code at each worksheet individually. I tried this code:
Sub LoopTest()
Dim Current As Worksheet
For Each Current In Worksheets
' here i pasted the macro "Test"
Next
End Sub
But it did not work. Can anybody give a hint on how should I make it run as it should?
Can anybody assist me with the following? I need to re-develop a macro that enters a formula into each worksheet within a workbook. The workbook has 25 worksheet and I need to insert a formulat that divided the last reading in the columns "C", "G", "E" and "I". Here is what I was able to come with:
Sub Test()
Dim rng As Range
Set rngC = Range("C" & Cells(Rows.Count, "C").End(xlUp).Row)
Set rngE = Range("E" & Cells(Rows.Count, "E").End(xlUp).Row)
Set rngG = Range("G" & Cells(Rows.Count, "G").End(xlUp).Row)
Set rngI = Range("I" & Cells(Rows.Count, "I").End(xlUp).Row)
rngC.Offset(1, 0).Value = rngC / 14.5
rngE.Offset(1, 0).Value = rngE / 14.5
rngG.Offset(1, 0).Value = rngG / 14.5
rngI.Offset(1, 0).Value = rngI / 14.5
End Sub
When I run the code, it work perfectely and inserts the formula into desired destination (divides last value in any of the columns "C", "G", "E" and "I"). But, what I can not do is make code loop so that the macro is applied for the entire workbook and spares me from running the-present code at each worksheet individually. I tried this code:
Sub LoopTest()
Dim Current As Worksheet
For Each Current In Worksheets
' here i pasted the macro "Test"
Next
End Sub
But it did not work. Can anybody give a hint on how should I make it run as it should?