Sleeper: Copy Data from several Workbooks to Another Workbook

Hello,
I need copy data from several workbooks (for example named A,B,C,…) to the one which will be summary. In workbooks there will be a list of workers by calendar week in columns. A separate column every week. The list of workers will not be the same every week. I need to have a list of workers from branches (workbooks A,B,C,…) in the summary workbook. Enclosed are my workbooks for testing. I am stuck when I want to copy data from 3rd workbook. I will have more workbooks from which I will need copy data. And it paste data to the row from column where there is the least data.

Any help would be greatly appreciated. If you need more information please ask.Thank you in advance.

OKay, I'll ask.... What exactly are we trying to copy from each workbook A, B, & C. Where exactly are we copying too in workbook Test?

Hello, thanks for your interest. In each workbook A, B, C, ... are same worksheets, named "Operátori" - this worksheets will be always at position 1. In worksheets at columns D, E, F,... will be list of workers for calendar week. Each column for one calendar week. One workbook is a list of workers from one branch. There are several branches. Every week I have to summarize the list of workers from each branch into one list.

Can you attach a sample workbook showing the before and after concept please?

Quote:

---

Originally Posted by Aussiebear

Can you attach a sample workbook showing the before and after concept please?

---

Workbooks A, B, C, are list with workers from branches. Test is summary. A copied it manually.

BTW. what version of excel are you using, and are all these workbooks to be found in a particular folder?

I will wait to see the excel version, on a side note - trying to extract the two zip files crashed my poor little laptop twice for some reason!

Nothing like that happened here, both had 4 small files, namely Workbooks A,B & C and Test. Apparently columns D, E & F are not the same in length, but contain names of Employees which may or may not be repeated. Each column apparently represents one week, the workbooks came from various unnamed branches. The sheets appear to be consistent in layout, with Sheet 1 ("Operatori") I think it was...

Maybe if we are able to copy the three columns from each first sheet of each workbook to the Master workbook ("Test") and then create the unique list. if its Office 365 then it not an issue, if its another version then that's a bit more complicated.

Quote:

---

Originally Posted by Aussiebear

BTW. what version of excel are you using, and are all these workbooks to be found in a particular folder?

---

Hello, I am using office 2016, latest update. but I hope during this year we change it to Office 365. Workbooks will be in same folder, path I know how to change.

Quote:

---

Originally Posted by georgiboy

I will wait to see the excel version, on a side note - trying to extract the two zip files crashed my poor little laptop twice for some reason!

---

.zip file made on macbook. Maybe this is the problem? I uploaded files at https://drive.google.com/drive/folde...We?usp=sharing

Today the zip file worked fine, I guess my laptop is to blame - I do push it hard at times.


Will workbooks A, B & C be open at the point of running the macro or are the files closed and in a specific folder?
If the files A, B & C are in a specific folder, is that folder seperate from where the master (Test) file is located?

Quote:

---

Originally Posted by georgiboy

Today the zip file worked fine, I guess my laptop is to blame - I do push it hard at times.


Will workbooks A, B & C be open at the point of running the macro or are the files closed and in a specific folder?
If the files A, B & C are in a specific folder, is that folder seperate from where the master (Test) file is located?

---

Workbooks A, B & C will be closed in specific folder. Master (Test) will be in same folder.

Hello, can anyone help? We changed version to Microsoft 365

On the worksheet 'Hárok1' in the Test workbook, Is the data that is already there to be deleted before importing from the A. B & C files or will that range already be blank?

Same kind of question, if week 20 & 21 already have data, are you only wantig to import the latest week, i.e. week 22?

I will try to explain what I have to do.

During the year, I have to report the total quantity of employees together with all branches at regular intervals. The list of employees is updated once a week. The summary list must also include lists from previous weeks.
Files A, B & C represent individual branches. The quantity of employees in individual branches is not the same. A new branch may be created during the year, in which the list of employees will start with a different week.
Files A, B & C are for demonstration. We currently have 9 branches, each with a separate file.

Could someone help me get this macro to work for Mac Microsoft 365?

Code:

---

Sub Makro1()
Application.ScreenUpdating = False
DestBook = ActiveWorkbook.Name
Files = Application.GetOpenFilename(FileFilter:="Excel files (*.xls*), *.xls*", Title:="Choose an Excel file to open", MultiSelect:=True) 
If Not IsArray(Files) Then   
    Exit Sub 
End If 
For Each File In Files   
    Col = 4   
    Workbooks.Open (File)   
    SourceBook = ActiveWorkbook.Name       
    DoWhile = True       
        Do While DoWhile = True   
        Windows(DestBook).Activate   
        Sheets(1).Cells(1, Col).Select   
        If Selection.Text = "" Then       
            EndRow = 1       
            StartRow = 1       
        Else       
            Range(Selection, Selection.End(xlDown)).Select       
            EndRow = Selection.Rows.Count + 1       
            StartRow = 2   
        End If         
        ' Do col3 vlozi nazov suboru   
        If Col = 4 Then       
            ActiveSheet.Cells(EndRow, 3).Value = SourceBook   
        End If         
        Windows(SourceBook).Activate   
        ActiveSheet.Cells(StartRow, Col).Select   
        If Selection.Text = "" Then       
            Exit Do   
        End If   
        Range(Selection, Selection.End(xlDown)).Select   
        Selection.Copy   
        Windows(DestBook).Activate   
        ActiveSheet.Cells(EndRow, Col).Select   
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False   
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False   
        Selection.PasteSpecial Paste:=xlPasteColumnWidths         
        Col = Col + 1       
    Loop   
    Windows(SourceBook).Close   
    Application.ScreenUpdating = True   
    Windows(DestBook).Activate   
Next           
Application.ScreenUpdating = True
End Sub

---