Macro to combine same-named sheets from multiple files

[virsilens]

Hi All,

I have 30 files in one folder, each of these files has 6 tabs identically named: dog, cat, bird, fish, fly, ape. I wish to combine only the ‘cat’ sheet from all 30 files into a single file, with the sheets renamed to cat1, cat2, cat3,…cat30 (or some sequential naming order)

Thank you in advance

[mehmetcik]

Put this macro in a new spreadsheet.
Save it in the folder
open it
Stand Back

Sub Auto_Open()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

MyPath = ActiveWorkbook.Path

MyName = ActiveWorkbook.Name

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder(MyPath)

Count = 0
For Each objFile In objFolder.Files
Temp = objFile.Name
If Right(objFile.Name, 5) = ".xlsx" Then
    
ChDir MyPath
Workbooks.Open Filename:=objFile.Name
count = count +1
    Sheets("Cat").Move After:=Workbooks(MyName).Sheets(Workbooks(MyName).Sheets.count)
    ActiveSheet.Name ="Cat-" & Count
    Windows(objFile.Name).Close False

End If

Next objFile

End Sub

[virsilens]

o..m..G!

My hero! This is fantastic! I've tried 20 macros today and none worked - your is flawless - cats everywhere! BIG THANKS

[virsilens]

o..m..G!

My hero! This is fantastic! I've tried 20 macros today and none worked - your is flawless - cats everywhere! BIG THANKS