Run Macro on specific sheets

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
132
Office Version
  1. 2013
Platform
  1. Windows
Hi,

I have a macro that does the following:-

VBA Code:
Sub A_RenameSheets()

Dim ws As Worksheet

Path = ThisWorkbook.Path & "\"

On Error GoTo err_chk

For Each ws In Worksheets

If Len(Trim(ws.Range("B4"))) > 0 Then ws.Name = ws.Range("B4").Text

Next ws

On Error GoTo 0

Exit Sub

err_chk:

MsgBox "Error #:" & Err.Number & ": " & Err.Description, vbOKOnly, "ERROR RENAMING " & ws.Name

Err.Clear

Resume Next

End Sub


Rather than running the macro on all the sheets in the workbook, I only want it to run on sheets that start Shee*

I am sure the line For Each ws In Worksheets needs to be changed, but I haven’t got a clue of what to do to just run on specific sheets

Any help would be appreciated.

Thanks
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Just change this line:
VBA Code:
If Len(Trim(ws.Range("B4"))) > 0 Then ws.Name = ws.Range("B4").Text
to this:
VBA Code:
If (Len(Trim(ws.Range("B4"))) > 0) And (Left(ws.Name, 4) = "Shee") Then ws.Name = ws.Range("B4").Text
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
For Each ws In Worksheets
   If ws.Name Like "Shee*" Then
      If Len(Trim(ws.Range("B4"))) > 0 Then ws.Name = ws.Range("B4").Text
   End If
Next ws
 
Solution

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
132
Office Version
  1. 2013
Platform
  1. Windows
Joe4, Fluff,

Thanks for the prompt responses, I will try out both solutions tomorrow and let you know.

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

ok (y)
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
132
Office Version
  1. 2013
Platform
  1. Windows
Joe4, Fluff,

Once again thanks for your assistance.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,992
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top