Consulting

Results 1 to 16 of 16

Thread: Sleeper: Copy Data from several Workbooks to Another Workbook

  1. #1
    VBAX Regular
    Joined
    Jun 2023
    Location
    Slovakia
    Posts
    9
    Location

    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.
    Attached Files Attached Files

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,040
    Location
    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?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Regular
    Joined
    Jun 2023
    Location
    Slovakia
    Posts
    9
    Location
    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.

  4. #4
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,040
    Location
    Can you attach a sample workbook showing the before and after concept please?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    VBAX Regular
    Joined
    Jun 2023
    Location
    Slovakia
    Posts
    9
    Location
    Quote Originally Posted by Aussiebear View Post
    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.
    Attached Files Attached Files

  6. #6
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,040
    Location
    BTW. what version of excel are you using, and are all these workbooks to be found in a particular folder?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,181
    Location
    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!
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2403, Build 17425.20146

  8. #8
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,040
    Location
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    VBAX Regular
    Joined
    Jun 2023
    Location
    Slovakia
    Posts
    9
    Location
    Quote Originally Posted by Aussiebear View Post
    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.

  10. #10
    VBAX Regular
    Joined
    Jun 2023
    Location
    Slovakia
    Posts
    9
    Location
    Quote Originally Posted by georgiboy View Post
    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

  11. #11
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,181
    Location
    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?
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2403, Build 17425.20146

  12. #12
    VBAX Regular
    Joined
    Jun 2023
    Location
    Slovakia
    Posts
    9
    Location
    Quote Originally Posted by georgiboy View Post
    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.

  13. #13
    VBAX Regular
    Joined
    Jun 2023
    Location
    Slovakia
    Posts
    9
    Location
    Hello, can anyone help? We changed version to Microsoft 365

  14. #14
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,181
    Location
    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?
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2403, Build 17425.20146

  15. #15
    VBAX Regular
    Joined
    Jun 2023
    Location
    Slovakia
    Posts
    9
    Location
    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.

  16. #16
    VBAX Regular
    Joined
    Jun 2023
    Location
    Slovakia
    Posts
    9
    Location
    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
    Last edited by Aussiebear; 11-16-2023 at 05:33 AM. Reason: edited the code layout and added code tags to supplied code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •