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.
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.
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
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
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
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:
You're welcome Giorgio
Have a good one yourself.
Don’t have an account yet? Register yourself now and be a part of our community!