Results 1 to 8 of 8

Macro Loop does not move between worksheets

Threaded View

  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

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