[Solved] VBA : Is there a way of running a macro for all th

    I'm sure there must be an easier way of running a macro on all the worksheets in a workbook. at present i am having to write a line for each worksheet. I've got some sheets with over 50 sheets.


    any help would be greatfully appreciated.

    didn't know whether this was solved or no so...


    Code
    Sub sheetstuff()
Dim ws As Worksheet
For Each ws In Worksheets
    'your code here i.e.
    MsgBox ws.Name
Next
End Sub


    Hope it helps

    This code seems to run my macro, the messagebox says the name of various spreadsheets but the macro continues to work on the same sheet. should I have a line that says move from from one sheet to the next. also the message box ask for a reply when calling each sheet. rather than the macro continuing.


    thanks for you help.


    Quote

    Originally posted by WillR
    didn't know whether this was solved or no so...


    Code
    Sub sheetstuff()
Dim ws As Worksheet
For Each ws In Worksheets
    'your code here i.e.
    MsgBox ws.Name
Next
End Sub


    Hope it helps

    What's your macro code ?


    You don't generally need to select the worksheet/range for the macro to be able to work on it - so it shouldn't matter... The MSGBOX was just to illustrate that my code was identifying each worksheet...


    If you post up the full code you are wanting to apply to each of your worksheets, I'm sure we can point out where it is going wrong...

    I suspect it is because I am using activesheet in my macro. I'll enclose a short bit as it is rather long.


    sorry for being so stupid, thanks for being so patient with my problem.


    here is my macro within yours.


    Sub layout1()
    '
    ' Keyboard Shortcut: Ctrl+q
    '
    Dim ws As Worksheet
    For Each ws In Worksheets
    Application.Run "PERSONAL.XLS!layout"
    MsgBox ws.Name
    Next
    End Sub


    Sub layout()
    '
    ' Keyboard Shortcut: Ctrl+r
    '
    Range("A6").Select
    Selection.Copy
    Range("F1").Select
    ActiveSheet.Paste
    Range("B30:D45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("F2").Select
    ActiveSheet.Paste
    Range("A6,C6:D6").Select
    Range("C6").Activate
    Application.CutCopyMode = False
    Selection.Copy


    end sub

    I have assumed you wanted A6 to go to F1 and C6:D6 to go to G1:H1 and B30:D45 to go to F2:H17


    Hence the following code will work


    Code
    Sub sheetstuff()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Range("A6").Copy ws.Range("F1")
    ws.Range("C6:D6").Copy ws.Range("G1")
    ws.Range("B30:D45").Copy ws.Range("F2")
Next ws
End Sub


    Hope this helps

    Hi Giorgio,


    In order that you can understand the nature of your problem, which Will has helped you to solve, you need to understand this:


    If you use an unqualified range reference, such as "Range("A6").Select", then Excel will assume that you are referring to the ActiveSheet. It is not necessary to loop through the sheets and activate each one, in fact it is better not to, you simply use the worksheet variable (ws in Will's example) to qualify the range references - like this "ws.Range("A6").Copy".


    Once you are comfortable with that concept you should then look at using With ... End With statements to avoid repeated references to the same object.


    HTH

    Here's an example written using With...End With... as per Richie's suggestion ( :wink2: )


    Code
    Sub sheetstuff2()
Dim ws As Worksheet
For Each ws In Worksheets
    With ws
        .[A6].Copy .[F1]
        .[C6:D6].Copy .[G1]
        .[B30:D45].Copy .[F2]
    End With
Next ws
End Sub

    I would just like to thank willr and richieuk for their time and patience helping me resolve my problems.


    have a great weeekend


    giorgio:tumble::tumble::tumble:

Participate now!

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

Register Yourself Login