VBA : Modifying Multiple Sheets using Sheet names

Rennie · Mar 26th 2004

Is it possible at all to modify a collection of sheets by using the sheet name.

I have a workbook that has about 20 sheets. There are 5 Kinds of sheets in each workbook. ie. 5 Assumption Sheets ("(1) Assump","(2) Assump","(3) Assump"...etc). Each kind is identical to each other.

I would like to know if I can use a wild card to select the group of sheets and edit them. I.e "*Assump" in a macro.

Thanks,
Rennie

**iwrk4dedpr** · Mar 26th 2004

I'm sure that there is a better way than performing the modifications 1 page at a time. So be on the lookout for a better solution.

However, until then here is a solution which you can use which will find the all the pages in the workbook which have the desired string in the page name

Code

Sub ModifySheets()


    Dim wksWorksheet As Worksheet
    Dim strSelSheetName  as String


    For Each wksWorksheet In ActiveWorkbook.Worksheets
        strSelSheetName = wksWorksheet.Name
        If InStr(strSelSheetName, "Assump") &gt; 0 Then
                .... your code here
        End If
    Next wksWorksheet


End Sub

Display More

Rennie · Mar 29th 2004

iwrk4dedpr

Thank you for your reply. For some reason this does not work on the first sheet of the workbook.

Thanks,
Rennie

VBA : Modifying Multiple Sheets using Sheet names

Participate now!

Share