none
macro Copying worksheets from multiple workbooks into current workbook RRS feed

  • Question

  •   How to copy  from a specific  folder "C:\Users\user\Documents\2017 DATABASE\ all  sheet tabs from excel files xlsx, xlsm xls , to active workbook .

     That is when macro is triggered from current workbook, it goes to a specific folder "C:\Users\user\Documents\2017 DATABASE\find all excel files  sheets , copy the  all sheets tabs  to the current workbook.

    Note that the closed workedbooks does not have  same sheet tabs  so there is no risks of duplicate tabs .

    The current workbook has only one sheet tabs named Control.

    My first option is to open the file as below  vba code .

    and then to copy all open workbook sheets  to current workbook where the macro will be triggered , the macro openFilesaa will be triggered  from the same current workbook .

    The current workbook has only one sheet tab named control where all macros will be triggered from that current sheet .

    And then closed the workbook except current workbook where all the sheets have been copied,

    There are some similarities as url below but does not do what I was expecting.

    https://www.get-digital-help.com/2012/10/02/copy-each-sheet-in-active-workbook-to-new-workbooks/#master

    'https://excel.tips.net/T007425_Combining_Worksheets_from_Many_Workbooks.html

    'https://www.extendoffice.com/documents/excel/456-combine-multiple-workbooks.html

    Option 1. copy sheet tabs from closed workbook to current book

    or 2 option  open workbook  from specific folder , copy all sheets  to current workbook and the closed the open workbook  from the specified folder 

    Thanks if someone can assist 

    Sub OpenFilesaa()
    Dim MyFolder As String
    Dim MyFile As String
    MyFolder = "C:\Users\user\2017 DATABASE\\test cateco"
    MyFile = Dir(MyFolder & "\*.xl*")
    
    Do While MyFile <> ""
        Workbooks.Open fileName:=MyFolder & "\" & MyFile
        MyFile = Dir
    Loop
    End Sub

    Monday, March 12, 2018 6:07 PM

Answers

  • Hi Jean_7,

    I made a sample and shared it via OneDrive, named "Jean_7_CollectSheets.zip".
    Please download and check if it works.

    [note]
    (1) This zip file contains a folder "Jean_7".
    (2) There 6 files in this "Jean_7" folder.
    (3) "CollectSheets.xlsm" is a main file for collecting sheets from other excel files.
        *** cell [A1] is for specifying target folder ** Please change according to your environment **
    (4) Other 5 files are target files which are collected.



    Regards,

    Ashidacchi >> http://hokusosha.com/

    • Edited by Ashidacchi Thursday, March 15, 2018 1:20 AM
    • Proposed as answer by Ashidacchi Friday, March 16, 2018 6:35 PM
    • Marked as answer by Jean_7 Saturday, March 17, 2018 11:55 AM
    Thursday, March 15, 2018 1:19 AM

All replies

  • Hi Jean_7,

    I made a sample and shared it via OneDrive, named "Jean_7_CollectSheets.zip".
    Please download and check if it works.

    [note]
    (1) This zip file contains a folder "Jean_7".
    (2) There 6 files in this "Jean_7" folder.
    (3) "CollectSheets.xlsm" is a main file for collecting sheets from other excel files.
        *** cell [A1] is for specifying target folder ** Please change according to your environment **
    (4) Other 5 files are target files which are collected.



    Regards,

    Ashidacchi >> http://hokusosha.com/

    • Edited by Ashidacchi Thursday, March 15, 2018 1:20 AM
    • Proposed as answer by Ashidacchi Friday, March 16, 2018 6:35 PM
    • Marked as answer by Jean_7 Saturday, March 17, 2018 11:55 AM
    Thursday, March 15, 2018 1:19 AM
  • Thank you  exactly what I was looking  for
    Saturday, March 17, 2018 11:56 AM
  • Thank you for marking as answer.

    Your question is really a good exercise for me.


    Ashidacchi >> http://hokusosha.com/

    Saturday, March 17, 2018 12:48 PM