How Can i get this macro to run on every tab not just the active tab?

[tonywatsonhelp]

Hi everybody,
In my excel workbook i have rectangle shapes that i move around the screen using a macro to highlight an area in a chart,
its pretty basic stuff its just a rectangle shape that moves according to the commands of the macros as shown below
(this is the one to move it up a notch! )

Sub zzBlueSquaremoveup11()
ActiveSheet.Unprotect
Application.ScreenUpdating = False
ActiveSheet.Shapes.Range(Array(Range("EY3"))).Select
Selection.ShapeRange.IncrementTop -10
ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub

This works perfectly,
however always looking to save time and energy i was wondering if i could improve it?
basically at the moment this works on the active workbook but every workbook is the same
so i was wondering if there would be a way to take this macro and when i run it, have it run on ever tab?

there are a few problems,
1 i don't know what the tabs will be called
2 there are 3 tabs i dont want to include called "Master" "Data" & "Charts"

so how can i get this macro to run on the selected tab then go through all the other tabs in the workbook other then the 3 i mentioned then return back to the current tab?

any ideas as i'm yet again a bit stuck!

Thanks

Tony

 

[scott_n_phnx]

You can try using an <b>Array</b> on the worksheets. You may need to modify the code that I have for you. I have not tested it with your code added in, but you should get the idea.

Sub zzBlueSquaremoveup11()
Dim ws as Worksheet
For Each ws In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4"))
	With ws
		.Unprotect
			Application.ScreenUpdating = False
		.Shapes.Range(Array(Range("EY3"))).Select
			Selection.ShapeRange.IncrementTop -10
		.Protect
			Application.ScreenUpdating = True
	End With
Next ws
End Sub

 

[Snakehips]

Tony,

Another option...

Sub zzBlueSquaremoveup11()
On Error Resume Next
For Each sht In ThisWorkbook.Sheets
Select Case sht.Name
Case "Master", "Data", "Charts"
Case Else
sht.Unprotect
Application.ScreenUpdating = False
sht.Shapes.Range(Array(Range("EY3"))).ShapeRange.IncrementTop -10
sht.Protect
End Select
Next sht
Application.ScreenUpdating = True
On Error GoTo 0
End Sub

Hope that helps

 

[tonywatsonhelp]

This is brilliant,
thanks to both scott n phnx and snakehips for you imput, not sure which i will use going to test and play around with both see what happens,
thanks very much this is going to save me a lot of time!

Thanks

Tony