Hi Everyone,
I have an Excel workbook with hundreds of worksheets of the same structure that I need to update regularly. I wrote a macro that I can run on these worksheets when stepping on them, but I would like to simplify the task further and put this macro in a loop, so I just have to click once and all these worksheets of same structure update.
I wrote the loop, but it is not working. The problem is that it is not updating any other worksheets than the one I am on when I start it. It starts flashing and runs for some time, but at the end I only get the current worksheet updated...it seems that the macro runs hundreds of times on the current worksheet, instead of stepping to all the other ones.
There are other worksheets in the workbook on which I do not want to run this macro, they all have - (hyphen) in their names, so I thought it is easy to filter them out with an IF statement.
Can someone please help me with this issue?
The code I am using is the following:
Sub WorksheetLoop()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If InStr("-", ws.Name) = 0 Then
ActiveWindow.SmallScroll Down:=-12
Range("A43:R53").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=6
Range("A56").Select
ActiveSheet.Paste
Rows("64:65").Select
Selection.EntireRow.Hidden = True
Range("B56:R56").Select
Selection.Replace What:="2014", Replacement:="2015", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("C58:D66").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("F58:H66").Select
Selection.ClearContents
Range("J58:L67").Select
Selection.ClearContents
Range("N58:O66").Select
Selection.ClearContents
Range("Q48").Select
ActiveCell.FormulaR1C1 = "=R[-3]C*4.33/R[-2]C"
Range("Q53").Select
ActiveCell.FormulaR1C1 = "=R[-4]C*4.33/R[-7]C"
Range("O45:O53").Select
Selection.Copy
Range("P45").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.Replace What:="Nov-14", Replacement:="Dec-14", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B58:B66").Select
Selection.Replace What:="Nov-14", Replacement:="Jan-15", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.SmallScroll Down:=-6
Rows("30:42").Select
Selection.EntireRow.Hidden = True
Range("O73").Select
End If
Next ws
End Sub
Bookmarks