Is there a reason that this macro does not jump to the next worksheet?

Aug 1st 2012

#1

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

dutchballa

Student

Points

500

Posts

46
Aug 1st 2012
- #2
Re: Is there a reason that this macro does not jump to the next worksheet?

When I step through the second macro, it does do what I want. The problem must be something that I did with the "next wks" piece in the first macro. I am not familiar enough with VBA to know what I am doing wrong.

Thanks.
Quote
vwankerl

Intermediate

Points

1,025

Posts

199
Aug 1st 2012
- #3
Re: Is there a reason that this macro does not jump to the next worksheet?

Try something:

put

Code

Application.ScreenUpdating = True

as the last line in the first macro (before the end sub)

I want to be sure that you are not mistaking the fact that the screen doesn't change with the macro not stepping through the sheets.
Quote
dutchballa

Student

Points

500

Posts

46
Aug 2nd 2012
- #4
Re: Is there a reason that this macro does not jump to the next worksheet?

Hello,

I had it that way before, and I am sure the macro is not stepping to the next worksheet. I was stepping through the macro one line at a time and was running into the same problem.

I figured out a workaround by having the edit_data code go to the next worksheet at the end of the code sequence instead of relying on the loop to step through the next worksheet.

Thank you.
Quote

Aug 2nd 2012

#5

Re: Is there a reason that this macro does not jump to the next worksheet?

The code could be re-written so you can pass the worksheet as an argument to Edit_data.

I've tried to do that but there were some parts of the code that kind of baffled me.

Code

Sub Edit_Data(ws As Worksheet)
    Application.ScreenUpdating = False
    With ws
    
        With .Range(.Range("B39:C39"), .Range("B39:C39").End(xlDown))
            .Copy
            .PasteSpecial Paste:=xlPasteValues
        End With


        .Range(.Range("C39"), .Range("C39").End(xlDown)).Cut .Range("F39")


        .Range("C39").Formula = "=TEXT(B39, ""mmmm"")"
        .Range("D39").Formula = "=YEAR(b39)"
        .Range("E39").Formula = "=c39&"" ""&d39"


        ' not sure what this part does
        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("G39").Formula = "=IF(ISERROR(F39/F40-1),0,(F39/F40-1))"
        .Range("G39").Copy


        ' not sure what's happening here
        .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 With


End Sub


Sub Edit_Data_Loop()


    
    Dim wks As Worksheet
    
    Application.ScreenUpdating = False
    
    For Each wks In ActiveWorkbook.Worksheets
        Edit_Data wks
         
    Next wks
    
End Sub

Display More

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login

Share

Navigation
OzGrid
Forum
1. Unresolved Threads
2. Forum Rules
Dashboard
Articles
Privacy Policy
Search
Options
Current Location
OzGrid Free Excel/VBA Help Forum
Forum
HELP FORUMS
Excel General

User Menu
Login or register

This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close

Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.