Macro to copy sheets from different files into single one

Question

I currently have a workbook for each person in my team where they have a worksheet named "Panel" that contains their initiatives and progress. I want to develop a unified spreadsheet containing all their initiatives to have a view of the whole area.

In each "Panel" sheet, the "U5" cell contains the name of the owner. In my consolidated file, I want to put the name of the owner as the name of the corresponding sheet.

I made this macro to get, from a separate folder where they will all put their individual sheets, all the "Panel" sheets, put them in the main file and rename them to identify the owner.

Later on, I'll develop a database with the initiatives, identifying the start and end of the data fields to compile them in a single manner for a dashboard.

This is my code:

Sub GetSheets()
    Path = "C:\Users\Admin\Desktop\PMO\Test consolidation\Independent files"
    Filename = Dir(Path & "*.xlsm")

    Dim wsname As String

    Do While Filename <> ""
        Workbooks.Open Filename:=Path & Filename, ReadOnly:=True

        Worksheets("Panel").Activate
        Sheet.Copy After:=ThisWorkbook.Sheets(1)
        Worksheets("Panel").Select
        wsname = Range("U5")
        Worksheets("Panel").Name = wsname

        Workbooks(Filename).Close
        Filename = Dir()
    Loop
End Sub

Can you help to identify why this is not working? Thanks!

Looks like you are missing a backslash between your path and wildcard. — braX, Dec 12 '17 at 12:46
There are a few things to look at: **1.** Try and qualify your workbooks and/or worksheets. **2.** You don't have to **`Activate`** or **`Select`** your worksheets once you have qualified them. **3.** When you say **`this is not working`**, what do you mean? Is it not copying the sheets over? are you not getting the values from `U5`? ... — Zac, Dec 12 '17 at 12:56

score 1 · Accepted Answer · edited Dec 12 '17 at 14:10

1

Here is an example which checks whether path has \ present, whether sheets exists (code a la Rory) and also whether U5 is empty. Assumes, U5 in workbooks you are opening are being used for renaming.

Option Explicit

Sub GetSheets()
    Dim path As String
    Dim Filename As String
    Dim wbMaster As Workbook
    Dim wbActive As Workbook
    Dim wsPanel As Worksheet

    Set wbMaster = ThisWorkbook

    path = "C:\Users\Admin\Desktop\PMO\Test consolidation\Independent files"
    If Right$(path, 1) <> "\" Then path = path & "\"
    Filename = Dir(path & "*.xlsm")

    Dim wsname As String

    Do While Filename <> ""     
        Set wbActive = Workbooks.Open(Filename:=path & Filename, ReadOnly:=True)

        With wbActive
            If Evaluate("ISREF('" & "Panel" & "'!A1)") Then 'Rory 'https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists
                Set wsPanel = wbActive.Worksheets("Panel")
                wsPanel.Copy After:=wbMaster.Worksheets(1)

                If Not IsEmpty(wsPanel.Range("U5")) Then
                    ActiveSheet.Name = wsPanel.Range("U5")
                Else
                    MsgBox "Missing value to rename worksheet in " & Filename
                End If
            End If
        End With

        wbActive.Close
        Filename = Dir()
    Loop
End Sub

edited Dec 12 '17 at 14:10

Pᴇʜ

56,719
10
49
73

answered Dec 12 '17 at 13:47

QHarr

83,427
12
54
101

Adding the backslash should happen directly after `path = …` otherwise `Dir(path & "*.xlsm")` will still fail! So you should swap the lines. – Pᴇʜ Dec 12 '17 at 14:07
Sorry. Typo. I was pasting into mobile and trying to delete /re-arrange some lines. – QHarr Dec 12 '17 at 14:08
Amazing, works like a charm! Many thanks for the help! – gosh Dec 12 '17 at 15:32
Just another quick question: in case I want to paste the sheets as values, why isn't this working? ... wsPanel.Copy After:=wbMaster.Worksheets(1) With wbMaster.Sheets(2) .PasteSpecial xlPasteFormats .PasteSpecial xlPasteValues End With – gosh Dec 12 '17 at 16:35
Have you tried recording a macro and then comparing the syntax? It is a little difficult from code in a comment to work out what is on new lines. – QHarr Dec 12 '17 at 16:46
it may be that after moving you need to select all cells and then convert to values – QHarr Dec 12 '17 at 16:51

Macro to copy sheets from different files into single one

1 Answers1