Sleeper: Copy Data from several Workbooks to Another Workbook

[Trolldi]

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.

[Aussiebear]

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?

[Trolldi]

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.

[Aussiebear]

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

[Trolldi]

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.

[Aussiebear]

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

[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!

[Aussiebear]

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.

[Trolldi]

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.

[Trolldi]

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

[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?

[Trolldi]

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.

[Trolldi]

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

[georgiboy]

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?

[Trolldi]

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.

[Trolldi]

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

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