Hello,
I have two macros. One of the macro is designed to loop through all the worksheets in a file and perform the same operations on them. For some reason, this macro is not jumping to the next worksheet and instead is looping through the same worksheet the whole time.
Here are both of the macros I am using. THe second macro is ugly, but it gets the job done.
Code
Sub Edit_Data_Loop()
Application.ScreenUpdating = False
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Application.Run ("Edit_Data")
Next wks
End Sub
Code
Sub Edit_data()
Application.ScreenUpdating = False
Range("B39").Select
ActiveCell.Range("a1:b1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C39").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("F39").Select
ActiveSheet.Paste
Range("C39").Select
ActiveCell.Formula = "=MONTH(B39)"
Range("D39").Select
ActiveCell.Formula = "=YEAR(b39)"
Range("E39").Select
ActiveCell.Formula = "=c39&"" ""&d39"
Range("C39:E39").Copy
Range("b39").Select
Selection.End(xlDown).Select
ActiveCell.Range("B1:D1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
ActiveSheet.Calculate
Range("c39:d39").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("c39").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="1", Replacement:="January", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="2", Replacement:="February", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="3", Replacement:="March", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="4", Replacement:="April", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="5", Replacement:="May", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="6", Replacement:="June", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="7", Replacement:="July", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="8", Replacement:="August", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="9", Replacement:="September", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="10", Replacement:="October", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="11", Replacement:="November", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="12", Replacement:="December", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("g39").Select
ActiveCell.Formula = "=IF(ISERROR(F39/F40-1),0,(F39/F40-1))"
Selection.Copy
Range("f39").Select
Selection.End(xlDown).Select
ActiveCell.Range("b1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("g38").Value = "Return"
ActiveSheet.Calculate
End Sub
Display More