CODEWITHSUNDEEP
vbaexcel
Timothy Alston
Timothy Alston

Reputation: 1549

VBA to remove prefix from excel tab names

I have an excel file with many tabs. All tabs are named such as: "pboro bz.csv", "pboro fd.csv", "pboro ha.csv", etc.

I am looking to rename all of these "bz.csv", "fd.csv", "ha.csv" instead.

Furthermore, depending on the data another VBA imports, the tab names may have different prefixes. I therefore have a cell (B8) in a sheet titled "Setup" with the prefix I wish to remove, in this case "pboro "

I am trying to use this code:

Sub RemovePrefix()
  Dim WS As Worksheet
  Dim Val As String
  Val = Setup.Range("B8").Value
  Const Prefix As String = Val
  For Each WS In Worksheets
    If WS.Name Like Prefix & "*" Then WS.Name = Mid(WS.Name, Len(Prefix) + 1)
  Next
End Sub

The code crashes on the line

 Const Prefix As String = Val

with the error "Constant expression required"

I suspect this is because I am using Dim with Val, then Const with Prexif. However when I adapt the code to the following:

Sub RemovePrefix()
  Dim WS As Worksheet
  Dim Val As String
  Val = Setup.Range("B8").Value
   For Each WS In Worksheets
   If WS.Name Like Val & "*" Then WS.Name = Mid(WS.Name, Len(Val) + 1)
  Next
End Sub

I receive the error "Object required" relating to my line of code

  Val = Setup.Range("B8").Value

I am unsure where I am going wrong (and being a relative VBA noob probably isn't helping).

Upvotes: 1

Views: 1213

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

Now it's crashing because you never set Setup as a worksheet.

Dim setup as Worksheet
Set setup = Sheets("Setup")

should do the trick. Also, it looks like Val will change, so you wouldn't want to set it as a Constant.

Upvotes: 3

Related Questions