Results 1 to 6 of 6

Running One Macro in Multiple Worksheets & Loop

Threaded View

  1. #1
    Registered User
    Join Date
    11-10-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    18

    Running One Macro in Multiple Worksheets & Loop

    Hey guys I am new on the forum and heard great things about it and how much some people have learned through this forum. I am relatively new to excel vba, and need help figuring out this macro so I'll go straight to the point. Thanks in advance for the help

    So I've built a macro for a report that I run every month. I wanted to know how I can run the same macro and in different tabs ( I have a totally of 22 tabs) I didn't want to create 22 macro's for this.

    I was also wondering if there is a way I can have this loop, in the sense that once it started in sheet one it can go to sheet 22 and stop.

    I've built this macro in the workbook that I am working with and would it be problem to move it to my personal macro workbook?

    And finally the ranges differ from sheet to sheet in the macro below the range goes to M5:M200 and A5: O200 but it can go up to M5:M5000 and A5:05000 should I just put in a high value that my macro could work against.

    Thank you all so much again for your help and inputs. Below is the macro I've built and trying to finish;

    
    Sub Macro11()
    '
    ' Macro11 Macro
    '
      Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Selection.Replace What:="#DIV/0!", Replacement:="0", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("A5:O5").Select
        Range(Selection, Selection.End(xlDown)).Select
        Application.CutCopyMode = False
        ActiveWorkbook.Worksheets("STIHL").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("STIHL").Sort.SortFields.Add Key:=Range("M5:M200") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("STIHL").Sort
            .SetRange Range("A5:O200")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    '
    End Sub
    Last edited by mjali001; 11-10-2011 at 12:05 PM.

Thread Information

Users Browsing this Thread

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

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