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

 

[Logit]

Untested here but should work :

Sub CommandButton1_Click()

Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
Dim lr As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "C:\Users\gagli\Desktop\New Folder\"    'edit path to workbooks to be copied
fileName = Dir(directory & "*.xls")


Do While fileName <> ""
    
    Workbooks.Open (directory & fileName)

    With Sheets("Staffing-Processes")
        .UsedRange.Copy
        
        'copied data to be pasted to master workbook in Sheet1. If sheet is named otherwise edit the name below, next line
        ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End With
    
    Workbooks(fileName).Close
    fileName = Dir()
Loop

Worksheets("Sheet1").Columns("A:Z").AutoFit
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.CutCopyMode = False
Sheet1.Range("A1").Select

End Sub

Place a CommandButton1 on Sheet1 ...

 

[Blanchetdb]

Untested here but should work :

Sub CommandButton1_Click()

Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
Dim lr As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "C:\Users\gagli\Desktop\New Folder\"    'edit path to workbooks to be copied
fileName = Dir(directory & "*.xls")


Do While fileName <> ""
   
    Workbooks.Open (directory & fileName)

    With Sheets("Staffing-Processes")
        .UsedRange.Copy
       
        'copied data to be pasted to master workbook in Sheet1. If sheet is named otherwise edit the name below, next line
        ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End With
   
    Workbooks(fileName).Close
    fileName = Dir()
Loop

Worksheets("Sheet1").Columns("A:Z").AutoFit
   
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.CutCopyMode = False
Sheet1.Range("A1").Select

End Sub

Place a CommandButton1 on Sheet1 ...

Hi

Awesome it works .... thanks

Just one small issue... in the MasterSheet there is already a header on Row 1. When the "Staffing-Processes" sheet copies over, it adds the header from each sheet, so the header is repeated from every worksheet. What can I add to the macro so that it only copies from A2:AZ2000 from each "Staffing-Processes" sheet and leaves out the header?

Also, each workbook is password protected (password: "Staffing"). where can I add that so that it doesn't ask me to enter the password every time?

your help with this is greatly appreciated

thanks

 

[Logit]

Again, not tested here. Hopefully this works.

Sub CommandButton1_Click()

Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
Dim lr As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "C:\Users\gagli\Desktop\New Folder\"    'edit path to workbooks to be copied
fileName = Dir(directory & "*.xls")
Password = ("password")                    '<--- edit password term here

Do While fileName <> ""
    
    Workbooks.Open (directory & fileName & Password)

    'With Sheets("Staffing-Processes")
        '.UsedRange.Copy
        
Dim rng
Set rng = Worksheets("Staffing-Processes").UsedRange
Intersect(rng, rng.Offset(1)).Copy

        'copied data to be pasted to master workbook in Sheet1. If sheet is named otherwise edit the name below, next line
        ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End With
    
    Workbooks(fileName).Close
    fileName = Dir()
Loop

Worksheets("Sheet1").Columns("A:Z").AutoFit
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.CutCopyMode = False
Sheet1.Range("A1").Select

End Sub

 

[Blanchetdb]

Again, not tested here. Hopefully this works.

Sub CommandButton1_Click()

Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
Dim lr As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "C:\Users\gagli\Desktop\New Folder\"    'edit path to workbooks to be copied
fileName = Dir(directory & "*.xls")
Password = ("password")                    '<--- edit password term here

Do While fileName <> ""
   
    Workbooks.Open (directory & fileName & Password)

    'With Sheets("Staffing-Processes")
        '.UsedRange.Copy
       
Dim rng
Set rng = Worksheets("Staffing-Processes").UsedRange
Intersect(rng, rng.Offset(1)).Copy

        'copied data to be pasted to master workbook in Sheet1. If sheet is named otherwise edit the name below, next line
        ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    End With
   
    Workbooks(fileName).Close
    fileName = Dir()
Loop

Worksheets("Sheet1").Columns("A:Z").AutoFit
   
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.CutCopyMode = False
Sheet1.Range("A1").Select

End Sub

the headers no longer duplicate .... great, THANK YOU

I am still required to enter the password for every workbook being opened.

 

[Logit]

Taking care of the password issue will require some time here. I don't have a simulated setup to test. I will work on it but if someone else already has the answer please
jump in and assist.

Thanks.

 

[Blanchetdb]

Taking care of the password issue will require some time here. I don't have a simulated setup to test. I will work on it but if someone else already has the answer please
jump in and assist.

Thanks.

Thank you very much for what you have already provided… it enables me to continue with my project. The password functionality is not a “must” but more a nice to have.

 

[Logit]

How are you password protecting the workbooks ? I see several different methods of protecting various sections of a workbook.

????

 

[Blanchetdb]

How are you password protecting the workbooks ? I see several different methods of protecting various sections of a workbook.

????

It is being protected from “File” not from Protect Workbook under the Review option. The Workbook requires a password in order to open the Workbook

 

[Logit]

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