Tweak to speed-up macro

MartinDH

New Member
Joined
Oct 16, 2018
Messages
3
Hello,

I am using a macro to rename all my file in a folder with a specific cell value, the macro also rename the sheetname with the same cell value.
It works fine for what I need, the only drawback is that the macro is sometime terribly slow.

1) Could someone give me some tweak to speed-up a bit this macro ?

2) I would like to make some change to the macro to be able to select only specific file in a folder to be renamed instead of the full content of the folder. Would it be possible ?

Thanks in advance for your help,
Martin

Sub RenameAllExcelFilesInDirectory()


With Application.FileDialog(msoFileDialogFolderPicker)
.Show
filepath = .SelectedItems(1)
End With


Set r = Workbooks.Add.Worksheets(1).Range("A1")
StrFile = Dir(filepath & "\*.*")


Do While Len(StrFile) > 0
strExtension = Split(StrFile, ".")(UBound(Split(StrFile, ".")))

Set wb = Workbooks.Open(filepath & "" & StrFile)
StrNewfullfilename = wb.Sheets(1).Range("B6").Value & "." & strExtension
Dim rs As Worksheet


For Each rs In Sheets
rs.Name = rs.Range("B6")
Next rs
ActiveWorkbook.Save

wb.Close

r.Value = StrFile 'print old name
r.Offset(, 1).Value = StrNewfullfilename 'print new name
Set r = r.Offset(1)
Name filepath & "" & StrFile As filepath & "" & StrNewfullfilename
StrFile = Dir
Loop


ActiveWorkbook.Close False




End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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
Back
Top