Macro On Multiple Worksheet, Different Ranges

  • I am looking for a way to modify the following macro so that I can run the same code for multiple ranges that are on different worksheets, any help GREATLY appreciated...




  • Re: Macro On Multiple Worksheet, Different Ranges


    Avis Guy


    Take the time to read the Forum Rules that you agreed to when you joined. You must use Code Tags when posting code on the Forum. I will add them this time, but future posts without them may well be deleted!

  • Re: Macro On Multiple Worksheet, Different Ranges


    When you say "multiple ranges," you're referring to this as cells that are spaced similarly apart but in different areas?

    Code
    Range("schedule!H4:H35,J4:J35,K4:K35,M4:M35,N4:N35,P4:P35,Q4:Q35,S4:S35,T4:T35,V4:V35,W4:W35,Y4:Y35,Z4:Z35,AB4:AB35")


    Just making sure. And how will you select the first cell in the series?


    One way to do this is use a reference like Cells(x, y) , where x is the row and y is the column. And then you can use something like Cells(x + 1, y + 1) as other references, I believe. For example, Cells(1, 1) would be the equivalent of A1. To say Range("A1:E5") in VBA, you'd use:

    Code
    Range(Cells(1, 1), Cells(5, 5))
    ' [B]or[/B]
    x = 1
    y = 1
    Range(Cells(x, y), Cells(x + 4, y + 4))


    For easy reference, you can quickly change the column headings to a R1C1 reference and back with this code:

    Code
    Sub LetterColumns()
      Application.ReferenceStyle = xlA1
    End Sub
    
    
    Sub NumberColumns()
      Application.ReferenceStyle = xlR1C1
    End Sub


    To apply this on various worksheets, you'd set up the sheetname as a variable. Or, if it's sequential sheets and you're lazy like me, you could leave the sheetname blank and do something like this for each sheet:

    Code
    ActiveSheet.Next.Select
    ' or to select the previous sheet
    ActiveSheet.Previous.Select


    Quote from Avis Guy

    I am looking for a way to modify the following macro so that I can run the same code for multiple ranges that are on different worksheets, any help GREATLY appreciated...




  • Re: Macro On Multiple Worksheet, Different Ranges


    This is Worksheet code and would need to be placed in each worksheet, or moved to a module & adapted to work on each sheet - what determines the necessary ranges?

  • Re: Macro On Multiple Worksheet, Different Ranges


    For some reason I can not put this code in the worksheet area. Is anyone willing to let me send the actual file and see if you can modify it into the modules or worksheet area. Basically what it does is convert any number, ie: 1234 into time with colons 12:34 in the cells that you enter for a range. In this particular worksheet the range is not constant for the whole worksheet or workbook so I had to enter in the cells manually that I wanted time conversion on. Please let me know if anyone can be of assistance. THANKS

  • Re: Macro On Multiple Worksheet, Different Ranges


    Avis Guy


    I think we need to know a little more about how you want to modify the code.


    What sheets/ranges are involved?


    By the way note there are 2 parameters passed to the code - Sh and Target.


    Sh is the sheet the change was made in, and Target is the range on that sheet the change was made in.

  • Re: Macro On Multiple Worksheet, Different Ranges


    I'll ask again, what cells?


    The 2 worksheets are structured differently and it doesn't look like the data is the same either.


    Please remember this is your workbook, your worksheets and your data.:)

Participate now!

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