Worksheet rename with file name it was imported from

Saab95

New Member
Joined
Mar 26, 2021
Messages
27
Office Version
  1. 365
Platform
  1. Windows
Hi there,

Browsed the forum before asking but couldn't find the solution, have the below vba to combine in a workbook, a sheet called 'Summary-Sheet ' from other workbooks.

Then I want the imported sheet to be renamed with the same name as the file name it has been imported from.. that where I'm stuck.

The import works fine, just no renaming. Seems that what I have tried in red is not what is needed.


Sub CombineWorkbooks()

Dim Path As String
Path = "N:\TSLsummary\details\"

Dim FileName As String
FileName = Dir(Path & "*.xlsx")

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While FileName <> ""
Workbooks.Open Path & FileName
For Each ws In ActiveWorkbook.Sheets
If ws.Name = "Summary-Sheet" Then
ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
ws.ActiveSheet.Name = "FileName"
End If
Next ws
Workbooks(FileName).Close
FileName = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,045
Office Version
  1. 365
Platform
  1. Windows
Remove the quotes from FileName, otherwise it's treating it as a string & not a variable. And also remove the "ws."
That said if your filenames are more than 31 characters you will get a problem.
 
Solution

Saab95

New Member
Joined
Mar 26, 2021
Messages
27
Office Version
  1. 365
Platform
  1. Windows
Remove the quotes from FileName, otherwise it's treating it as a string & not a variable. And also remove the "ws."
That said if your filenames are more than 31 characters you will get a problem.
Thanks and guess what, my files have more than 31 chrts. 🤦‍♂️
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,045
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,148,339
Messages
5,746,177
Members
423,998
Latest member
eakenila

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