select sheets based on name

[snowman1976]

hello
I have a workbook that has multiple tabs. One of the macro's in it selects some of the tabs to do some modifications on a weekly basis.
Each time I add/delete/change a tab name I need to remember to step inside the VBA to make the corresponding change.
Is there a way I could write the VBA so it selects the tabs I want automatically? For instance, if I put the word "plan" in front of all the tabs I want, could it select them based on this ?

your help is always appreciated. I would be lost without this forum!

 

[tolga]

I think this will work, can you try it and see? put plan in front of each sheet name and I'm pretty sure it will work

Dim ws As Worksheet

    For Each ws In ThisWorkbook.Sheets
        If ws.name Like "plan" & "*" then

'insert whatever you want it to do here like : range("a1") = "hello"

end if 

next

 

[Stridhan]

yes
easier to guide you if you can post the code you've got

 

[Stridhan]

qouted wrong

 

[Stridhan]

I think this will work, can you try it and see? put plan in front of each sheet name and I'm pretty sure it will work

Dim ws As Worksheet

    For Each ws In ThisWorkbook.Sheets
        If ws.name Like "plan" & "*" then

'insert whatever you want it to do here like : range("a1") = "hello"

end if 

next

Think this will only affect activesheet.
Specify the range with sh.range("A1") instead

 

[tolga]

Think this will only affect activesheet.
Specify the range with sh.range("A1") instead

oops. sorry. yes, Stridhan is correct. ws.range etc or whatever you decide to do on the sheet, you have to specify that it's for ws before your action

 

[hiker95]

snowman1976,

Here is another macro solution for you to consider, with the sheet names beginning with plan (lower case p):

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub LoopThruPlanSheets()
' hiker95, 05/28/2015, ME857592
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If ws.Name Like "plan*" Then
    With ws
      
      'do something - insert the rest of your macro code here
      
    End With
  End If
Next ws
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the LoopThruPlanSheets macro.


Can we see the rest of your macro code?

 

[snowman1976]

hi all
sorry for the slow response, I was pulled off this project but I need to close it out.
I still cannot get this to work, I am hoping someone can point out the error. My code is this:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
If ws.Name Like "plan" & "*" Then
With ws
Columns("B:R").Select
Selection.EntireColumn.Hidden = False
Columns("R:R").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:Q").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

End With
End If

Next

 

[hiker95]

snowman1976,

Thanks for the rest of your macro code.

The following macro code works in my environment.

Here is another macro solution for you to consider, with the sheet names beginning with plan (lower case p):

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Compare Text
Sub LoopThruPlanSheets_V2()
' hiker95, 06/02/2015, ME857592
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If ws.Name Like "plan*" Then
    With ws
      .Activate
      .Columns("B:R").Select
      Selection.EntireColumn.Hidden = False
      .Columns("R:R").Select
      Selection.Copy
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      .Columns("C:Q").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End With
  End If
Next ws
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the LoopThruPlanSheets_V2 macro.

 

[hiker95]

snowman1976,

If my last macro code does not work correctly, then, we will need to see your actual workbook/worksheets.

You can upload your workbook to (the BLUE link-->) Box Net ,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.