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