Worksheet rename with file name it was imported from

Saab95

New Member
Joined
Mar 26, 2021
Messages
40
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
Solution
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. ?‍♂️
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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