Using For Loop to Call the Same Named Functions from Multiple Worksheets-CodePudding

I am trying to see if it's possible to click a button on a series of sheets with a function. For a single sheet, my code works fine, but I get an Runtime error 438 when I try to do the code below.

Public Sub Read_All_Data_Click()

Dim ws As Worksheet
For Each ws In Worksheets

ThisWorkbook.Sheets(ws.Name).Read_Data_Click

Next ws

End Sub

CodePudding user response：

ws is defined as worksheet, during the for loop the variable will be updated to be the current worksheet. Because of this using Sheets(ws.name) is redundant, instead your code should look like:

Public Sub Read_All_Data_Click()

Dim ws As Worksheet For Each ws In Worksheets

ws.Read_Data_Click

Next ws

End Sub

CodePudding user response：

Read_Data_Click is not a built in property of a worksheet.

You have to write your Sub Read_Data_Click(MySheet as worksheet) in a module, and parse the referance to each wc from the loop. Then you only use the name of the sub like Read_Data_Click(ws)