Copy a specific Worksheet from multiple Workbooks into one Workbook - one Worksheet

[Blanchetdb]

Hi

So, I have multiple workbooks (they are called: Human Resources, Science, Corporate Management, Public Affairs, .... I intend to add more). Each workbook has a sheet called: "Staffing-Processes". The data in each sheet has several rows (over 500 in some of them)

I would like to be able to copy that specific sheet, from each workbook, into one workbook - all into one worksheet.

The destination workbook is called: Master Database

I would like to be able to open the Master Database Workbook, "click" a command button and the Sheet ("Staffing-Processes") from each Workbook would copy to the Master Database - in the tab called "Master-Processes".

Can someone provide some assistance?

thanks

 

[Blanchetdb]

So the workbooks contain VBA code in a macro that protects the workbook from being opened ?

No…. There is no macro protecting the Workbook

If you click file at the top left, you can select to password protect the workbook requiring one to enter a password in order to open it. This method does not affect sheets or cells within the workbook itself

 

[Logit]

Thanks

 

[Logit]

I created a scenario here that matches what you have on your machine. Tried using the previous macros I posted ... you indicated they worked for you except for a few changes that were needed.
I don't understand ... none of them worked here.

Did some more research online and enlisted the help of Microsoft with some sample macros they have posted on the internet. Surprise surprise ! The few macros I tried from them did not work
either. So I did what guys never do .... went out on the internet and asked for help.

The following works here ... flawlessly. I trued it will work for you as well. The MASTER workbook needs to be off by itself - don't place it in the same folder as all the other workbooks you are copying from.

The various workbooks you are copying from must not have a password.

Sub CommandButton1_Click()
    ' Change folder path as needed, keep the trailing backslash
    Const sFolder = "C:\Users\gagli\OneDrive\Desktop\Test\"     '<<<<<<<<<<<<<<<   edit as required
   
    Dim sFile As String
    Dim mstWS As Worksheet
    Dim srcWB As Workbook
    Dim srcWS As Worksheet
    Dim t As Long, s As Long, srcNextRow As Long
   
    Application.ScreenUpdating = False
   
    ' Target sheet
    Set mstWS = ThisWorkbook.Worksheets("Sheet1") ' or use ActiveSheet
   
    ' First available target row
    t = mstWS.Range("A" & mstWS.Rows.Count).End(xlUp).Row
   
    ' Get first Excel filename in the folder
    sFile = Dir(sFolder & "*.xls*")
   
    ' Loop through the files
    Do While sFile <> ""
       
        ' Open source workbook
        Set srcWB = Workbooks.Open(sFolder & sFile)
       
        If Not srcWB Is ThisWorkbook Then       '   don't use this WB <<<<<<<<<<<<<<<<
       
            ' Refer to the first sheet
            Set srcWS = srcWB.Worksheets(1)
           
            ' Get the last used row
            srcNextRow = srcWS.Range("A" & srcWS.Rows.Count).End(xlUp).Row + 1
           
            ' Copy range
            srcWS.Range("A2:Z" & srcNextRow).Copy Destination:=mstWS.Range("A" & t)
           
            ' Increment target row
            t = t + srcNextRow - 2       '   <<<<<<<<<<<<<<<<< is this what you wanted
           
            ' Turn off clipboard
            Application.CutCopyMode = False
           
            ' Close source workbook
            srcWB.Close SaveChanges:=False
        End If
       
       
        ' Get next filename
        sFile = Dir
    Loop
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub