+ Reply to Thread
Results 1 to 8 of 8

Macro Loop does not move between worksheets

Hybrid View

atoga Macro Loop does not move... 02-27-2015, 12:50 AM
MarvinP Re: Macro Loop does not move... 02-27-2015, 12:55 AM
atoga Re: Macro Loop does not move... 02-27-2015, 01:42 AM
nilem Re: Macro Loop does not move... 02-27-2015, 02:02 AM
atoga Re: Macro Loop does not move... 03-01-2015, 08:47 PM
Alf Re: Macro Loop does not move... 02-27-2015, 02:21 AM
nilem Re: Macro Loop does not move... 02-27-2015, 02:27 AM
Alf Re: Macro Loop does not move... 02-27-2015, 02:39 AM
  1. #1
    Registered User
    Join Date
    02-27-2015
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    18

    Macro Loop does not move between worksheets

    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
    Last edited by atoga; 02-27-2015 at 01:38 AM. Reason: code tags left out

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,375

    Re: Macro Loop does not move between worksheets

    Hi atoga and welcome to the forum,

    Edit your above post and select the code part and then click on the "#" icon above the message area. This will put code tags around the code and the moderators won't give you demerits.

    Then look at:
    If InStr("-", ws.Name) = 0 Then
    and https://msdn.microsoft.com/en-us/lib...(v=vs.90).aspx
    and reverse the "-" and ws.Name

    That is just the first think I caught but without you putting your code in tags it is too hard to read.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    02-27-2015
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    18

    Re: Macro Loop does not move between worksheets

    Thanks MarvinP,
    I have changed the InStr Function. And I still have my basic issue with the macro not running on the worksheets as a Loop. Thanks for anyone who can help me with this...

    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

  4. #4
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Macro Loop does not move between worksheets

    maybe so
    ...
    For Each ws In ActiveWorkbook.Worksheets
        If InStr(ws.Name, "-") = 0 Then
            ws.Activate
    ...
    and try to get rid of 'Select'

  5. #5
    Registered User
    Join Date
    02-27-2015
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    18

    Re: Macro Loop does not move between worksheets

    Thanks a lot Nilem! I just inserted ws.Activate and it works. I did not get rid of Select, though.

  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,790

    Re: Macro Loop does not move between worksheets

    Should not line "ws.Activate" come before line "If InStr ..."?

    Alf

  7. #7
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Macro Loop does not move between worksheets

    @Alf,
    I think that not necessarily activate the worksheet to get the name of the sheet in this case

  8. #8
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,790

    Re: Macro Loop does not move between worksheets

    activate the worksheet to get the name of the sheet
    No that's right but the OP wanted the macro to loop through all sheets in the workbook.

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Have a loop macro loop for all visible worksheets
    By Graeme-Black in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-22-2014, 11:01 AM
  2. [SOLVED] Macro to Move Rows to New Worksheets Based on Criteria & Then HIDE & Move Back
    By abro0821 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-22-2014, 04:00 PM
  3. [SOLVED] Macro to move between worksheets
    By banga101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-11-2014, 04:09 AM
  4. Macro to loop through and move data to new tabs
    By tso6359 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2011, 03:05 PM
  5. Macro to loop across files, move some records
    By SueWithQuestion in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-26-2010, 09:33 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1