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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
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
67,826
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,587
Messages
5,765,300
Members
425,271
Latest member
kristyfinn

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