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/folders/1Ox3ldIT92x3VqQXf-8bK1_-y3UnmeYWe?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