CODEWITHSUNDEEP
vbaexcel
AKER
AKER

Reputation: 1

Merging workbooks by tab name

I am hoping someone is able to help.

I am looking at merging several workbooks in excel together but each workbook has 8-10 tabs that I don't need and I in fact only want a single tab, I have the following formula already which combines the workbooks, but I am unsure what to do to only combine the single tabs that I need, they are all the same name in each workbook. 

Sub GetSheets()
Path = "C:\Users\dt\Desktop\dt kte\"
Filename = Dir(Path & "*.xls")

  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
    Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
  Loop
End Sub

Thank you

Upvotes: 0

Views: 249

Answers (2)

user3598756
user3598756

Reputation: 29421

it's one of those cases where I'd use the infamous On Error Resume Next statement consciously and avoid looping through a collection (namely Worksheets one)

furthermore ThisWorkbook always references the workbook the running code resides in so there's no need for any Workbook type variable referring to it

Sub GetSheets()        
    Path = "C:\Users\dt\Desktop\dt kte\"
    Filename = Dir(Path & "*.xls")

    On Error Resume Next
    Do While Filename <> ""
        Workbooks.Open(Filename:=Path & Filename, ReadOnly:=True).Worksheets("SHEETNAME").Copy After:=ThisWorkbook.Sheets(1)
        ActiveWorkbook.Close False
        Filename = Dir()
    Loop
End Sub

just change SHEETNAME to you actual needed tab name

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

If you only need to copy a specific sheet from each, the following should work (just tweak SHEETNAME to whatever that is)

Sub GetSheets()
dim mainWB as Workbook
Set mainWB = ThisWorkbook
Path = "C:\Users\dt\Desktop\dt kte\"
Filename = Dir(Path & "*.xls")

  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
    If sheet.Name = "SHEETNAME" then
        Sheet.Copy After:=mainWB.Sheets(1)
    End if 
  Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
  Loop
End Sub

Upvotes: 0

Related Questions