Applying function to multiple worksheets based on array of names

supergarebear

New Member
Joined
Nov 18, 2015
Messages
5
I'm trying to create a function that can be applied to multiple worksheets based on the worksheet name. I know what function I want to apply, but I can't seem to figure out how to organize the process

Layout: You have 3 shapes in each worksheet (M1,M2,M3). M1 links to Sheet A1B1, M2 links to A2B1, M3 links to A3B1, this is the same to all worksheets.

I want a button to format the workbook by hiding the shapes (M1-3) based on if the linked sheets (A1B1,A2B1,A3B1) are hidden or not. The worksheets this applies to depends on the values I put into the array

Example:


ArrayA=1,2,3
ArrayB=1,3

Worksheet A2B1 is hidden
Target shape to hide: M2


In this case, because A2B1 is hidden, the macro would hide shape M2 to worksheets A1B1, A1B3, A2B1, A2B3, A3B1, A3B3, leaving all the B2 sheets alone.



I tried to create a macro first just to apply it to all pages, but even that is not working. I know it's a complicated request but it would make my work a lot easier than typing out every combination of actions


Code:
Sub testhide()

Dim ws as worksheet
Dim numA as long
Dim numB as long 
Dim shape as string

Set ws = sheets("A" & numA & "B" & numB)
Set shape = "M2"

For numA = 1 to 3
For numB = 1 to 3
For each ws in worksheets

If Worksheets("A2B1").Visible = False Then
ws.Shapes.Range(shape).Visible = False
End If

Next ws
Next numA
Next numB

End Sub

Workbook layout:

Worksheet NameShape Names in Worksheet
A1B1M1
M2
M3
A1B2M1
M2
M3
A1B3M1
M2
M3
A2B1M1
M2
M3
A2B2M1
M2
M3
A2B3M1
M2
M3
A3B1M1
M2
M3
A3B2
M1
M2
M3
A3B3M1
M2
M3



<tbody>
</tbody>
 

Forum statistics

Threads
1,085,842
Messages
5,386,292
Members
401,992
Latest member
CleverHopper

Some videos you may like

This Week's Hot Topics

Top