Change excel sheet name to excel file name automatically

0

I would like to rename the excel sheet name to the name of the excel file name automatically. I have one file called Test.xls and in this one sheet called Test010720161010. I need to change the sheet name to Test. I have multiple files and need to do this automatically.

Thanks

Answer
Discuss

Discussion

So you want one macro that runs and changes the name of every worksheet in all workbooks that are located in a single directory? How do you want it to work?
don (rep: 1247) Jul 20, '16 at 12:41 pm
Add to Discussion

Answers

0

Try this - forgot to mention that I probably lifted 99% of this from other forums - but that's how I learned.

Sub wbnme_wsnme()
Dim fName As String
Dim wrksht As String
Dim path As String
Dim strFile As String

'   for better speed turn off screen but make sure calculation updates are automatic
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .DisplayStatusBar = False
        .EnableEvents = False
        .AlertBeforeOverwriting = False
    End With
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
   
On Error Resume Next

'   open worksheet with path and filename
    Workbooks.Close
    Workbooks.Open ("Q:\Q316\Data\FilesInFolder.xlsx"), UpdateLinks
    Sheets("Sheet1").Select
    With ActiveSheet
        lastrw = .UsedRange.Rows.Count
        lastcol = .UsedRange.Columns.Count
        i = 2
        For i = 2 To lastrw
        path = Range("$B" & i).Value
        fName = Range("$A" & i).Value
                If .Range("B" & i).Value <> .Range("B" & i = 1).Value Then
                    iend = i
                End If
        strFile = path & fName & ".xlsx"
            With Workbooks.Open(strFile, UpdateLinks:=False)
                With Workbooks.Open(strFile, UpdateLinks:=False)
                    Worksheets(1).Name = wrksht            '  this assumes that you want to rename sheet 1
                ActiveWorkbook.Save
                ActiveWorkbook.Close
            End With
    Workbooks("Q:\Q316\Data\FilesInFolder.xlsx").Worksheets("Sheet1").Activate
    Sheets("Sheet1").Select
   
    End With
    Next
    End With
    Workbooks.Close
'   reset to default
    With Application
        .AlertBeforeOverwriting = True
        .ScreenUpdating = True
        .DisplayAlerts = True
        .DisplayStatusBar = True
        .EnableEvents = True
    End With
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

FilesInFolder columns:

File Name: Path: Final Name Save Path: 101 Q:\Q316\Data\Split\ 101 Final.xlsx Q:\_Mapping\101\ 102 Q:\Q316\Data\Split\ 102 Final.xlsx Q:\_Mapping\102\

Discuss
0

Michael01, I was going to put it as an comment but I used more than 1500 characters, Don please forgive me I know that this should be a comment

I'm just going to help you with this code that's why I didn't put an answer

First, please use CODE button and replace Code_Goes_Here with your code

 (I will not delete but I will make them comments by put ' before them)

 Sub wbnme_wsnme()

 'you used this variable

 Dim lastrw as integer
 Dim fName As String
 'you don't need this
 'Dim wrksht As String
 Dim path As String
 Dim strFile As String

 '   for better speed turn off screen but make sure calculation updates are automatic
     With Application
         .ScreenUpdating = False
         .DisplayAlerts = False
         .DisplayStatusBar = False
         .EnableEvents = False
         .AlertBeforeOverwriting = False
     End With
     
 'you did that before so just delete these
  '   Application.ScreenUpdating = False
  '   Application.DisplayAlerts = False
  '   Application.EnableEvents = False
    
 On Error Resume Next

 '   open worksheet with path and filename, why you closed open workbooks?
   '  Workbooks.Close
     Workbooks.Open ("Q:\Q316\Data\FilesInFolder.xlsx"), UpdateLinks

 'Selecting anything in vba will make the macro slower so just Replace selecting and activate with the sheet like this, and use sheets (1) so you indicate the sheet by it's number so you could change sheet name later .

  '   Sheets("Sheet1").Select
     With Sheets(1) 'ActiveSheet

         lastrw = .UsUsUsedRange.Rows.Count
 'you don't need this too
 '        lastcol = '.UsedRange.Columns.Count
 'You don't need to make i=2 because you typed it in the For, I also put . Before the range so that range refer to that sheet (that you typed after with)
  '      i = 2
         For i = 2 To lastrw
         path = .Range("$B" & i).Value
         fName = .Range("$A" & i).Value
 'I didn't understand what this will help
 '                If .Range("B" & i).Value <> '.Range("B" & i = 1).Value Then
 '                   iend = i
 '                End If
 'you should put the full name with the extention because there are more than one
         strFile = path & fName '& ".xlsx"
             With Workbooks.Open(strFile, UpdateLinks:=False)
 'You did that too
  '               With 'Workbooks.Open(strFile, 'UpdateLinks:=False)
 'another . Here, and wrksht is null and you don't need it so replace it with fName 
                     .Worksheets(1).Name = fname 'wrksht              this assumes that you want to rename sheet 1
                 ActiveWorkbook.Save
                 ActiveWorkbook.Close
             End With
     Workbooks("Q:\Q316\Data\FilesInFolder.xlsx").Worksheets("Sheet1").Activate
 'the same just put 1 so you could change name later.
     Sheets(1).Select
     
     End With
     Next
     End With
     Workbooks.Close
 '   reset to default
     With Application
         .AlertBeforeOverwriting = True
         .ScreenUpdating = True
         .DisplayAlerts = True
         .DisplayStatusBar = True
         .EnableEvents = True
     End With
 'the same you just did that
  '   Application.ScreenUpdating = True
  '   Application.DisplayAlerts = True
  '   Application.EnableEvents = True
 End Sub



 And to make this macro work you need to make that file have all workbooks that you want edit
Q:\Q316\Data\FilesInFolder.xlsx

(And don't forget to change this path to your needs)


In Column A the path to file and in Column B file names starting from the row 2.

So this code will work (if you made that file)

Please Michael01 edit your answer because I'm going to delete this answer,

And if all the files are in the same folder (don asked you about that) I could help you with a macro to make that file

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login