Copy and combine sheets to a workbook

1
votes

I need VBA code for Excel which: will be activated by a button in an empty workbook, loop through open workbooks, copies only sheets called "specificsheetname" from workbooks and pastes it into a new worksheet in the button activator workbook. So idea is that it will combine many worksheets from different workbooks into a one workbook. I tried this:

Sub workbookFetcher()

Dim book As Workbook, sheet, wsNew, wsCurr As Worksheet

Set wsCurr = ActiveSheet

For Each book In Workbooks
    For Each sheet In book.Worksheets
        If sheet.Name = "COOLING_RAW" Then
            Set wsNew = Sheets.Add(After:=wsCurr)
            book.Worksheets("COOLING_RAW").Copy
            Set wsNew = book.Worksheets("COOLING_RAW")
        End If
    Next sheet
Next book

End Sub

It kind of works but it pastes all the copied worksheets to a new workbook. That's not what I want, I want them to pasted in the same workbook.

3
vbaexcel
Two different questions need to be posted as two separate questions... but only after you search Google and this site for existing answers to similar questions. These are very common tasks. If you are stuck on something specific, you need to include your code along with sample data, specifics about what you've tried, and some background. See "minimal reproducible example" as well as "How to Ask". – ashleedawg
Edited to be just one question. All I found from the Internet was something that pastes the data to new workbooks. – nh3
You're adding a new worksheet then setting the worksheet equal to that. I think what you actually want to do is book.Worksheets("COOLING_RAW").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) or something along those lines. No need for the Set wsNew stuff at all. – user1274820

3 Answers

1
votes

As I said in my comment:

Sub workbookFetcher()

Dim book As Workbook, sheet as Worksheet

For Each book In Workbooks
    For Each sheet In book.Worksheets
        If sheet.Name = "COOLING_RAW" Then
            book.Worksheets("COOLING_RAW").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        End If
    Next sheet
Next book

End Sub

If you want it to be after the ActiveSheet and the ActiveSheet is in the middle of other sheets, you can still use your wsCurr and just increment the index.

0
votes

If you've got Excel 2016, then the newly bundled PowerQuery functionality under the Get & Transform part of the ribbon is by far the best way to do this. Suggest you google something like PowerQuery Combine Workbooks and you'll see heaps of great tutorials showing you exactly what to do. It pretty much makes lots of VBA redundant, and it is childs-play to learn compared to VBA.

If you've got any other version of Excel from 2010 up and have admin rights on your machine, you can download and install PowerQuery from Microsoft's site...it's a free add-in

0
votes

you don't need to iterate through each single workbook worksheets, while you just try to get the wanted sheet and copy it if it actually exists

moreover you want to avoid searching ThisWorkbook itsel for wanted worksheet, too! 

Option Explicit

Sub workbookFetcher()
    Dim book As Workbook, sht As Worksheet

    For Each book In Workbooks
        If book.Name <> ThisWorkbook.Name Then ' skip ThisWorkbook and avoid possible worksheet duplication 
            If GetWorksheet(book, "COOLING_RAW", sht) Then sht.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) ' if currently searched workbook has wanted worksheet then copy it to ThisWorkbook
        End If
    Next
End Sub

Function GetWorksheet(book As Workbook, shtName As String, sht As Worksheet) As Boolean
    On Error Resume Next ' prevent subsequent statement possible error from stoping the function
    Set sht = book.Worksheets(shtName) ' try getting the wanted sheet in the passed workbook
    GetWorksheet = Not sht Is Nothing ' return 'True' if successfully got your sheet in the passed workbook
End Function