0

I want to rename the worksheets based on two different variables, part of the file name and part of a cell's content

I will have a total of five worksheets per file, later saving as PDFs once the data is gathered and entered.

From the filename, I will use the first 3 or 4 numeric characters. (picture 1)

From Cell I1, I will use the words before "UPHOLSTERY" or approx the first 5 to 11 characters depending on the word. (picture 2)

Picture 3 shows the final results.

3 or 4 numeric characters

Cell I1 information

Final Results

DOC_CNTL_GUY
  • 3
  • 1

2 Answers2

-1

Here's a macro that should do the trick

Sub worksheetRename()
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ThisWorkbook
    'split the name of the file into words
    na = split(wb.Name, " ")
    'loop thru all worksheets
    For i = 1 To wb.Worksheets.Count
        Set ws = wb.Sheets(i)
        'split cell A1 into words
        nb = split(ws.Cells(1,1), " ")
        'set name of worksheet
        ws.Name = na(0) & " " & nb(0) 
    Next i
    a = MsgBox("Done", vbInformation)
End Sub
Peter Brand
  • 153
  • 7
  • I attempted this macro and get an error "Can't execute code in break mode" It occured on the ws.Name = na(0) & " " & nb(0) – DOC_CNTL_GUY Nov 05 '20 at 16:40
  • That message occurs if another macro attempts to run while you are stepping thru code in debug mode. Might be some code that runs on regular basis. Do an internet search to find more. I tested the code above and it works. – Peter Brand Nov 07 '20 at 09:08
  • My macro gets interrupted by another macro, you get a warning, so you down-vote my answer. Makes me wonder why I try and help people. – Peter Brand Dec 13 '20 at 07:40
-1

In each worksheet cell I1, enter formula :

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,255)&MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+4,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-9)
bosco_yip
  • 831
  • 1
  • 5
  • 5