Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post VBA Code to Open Workbooks saved in a common folder and copy data into destination workbook

    Hi Experts
    I am facing issue with something similar to the following thread - http://windowssecrets.com/forums/sho...ation-workbook
    My requirement is little different. In my case I have more than 14 workbooks saved in a common folder. Each workbook contains data 3 sheets/tab in it. I want data say sheet1 from all the 14 workbooks to be saved in a new workbook with its same tab name in a sequence. I mean the new sheet will have 14 sheets one after the other.
    I want sheet2 in same passion in a new workbook and Sheet3 as well in a new workbook.
    Please let me know if I am unclear here.
    Regards,
    JD

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Below is what I think you are looking for.

    Prior to running the macro "MyCopy"


    1 You will need to have open 3 Excel files with one worksheet
    A - Test of Master.xlsm
    B - Test of Master2.xlsx
    C - Test of Master3.xlsx

    2 The Macro is set to begin in the Workbook "Test of Master.xlsm"
    Worksheet 1 Cell "C15"
    This cell and all the cells below it must have the full pathname of the Worbooks with Data

    3 The fastest way to get this pathnames is to use Windows Explorer and go to the Folder with the files
    Select all the files CTRL + A
    Hold down "SHIFT" KEY and Right Click mouse.
    Select "Copy as path"
    Retun to the Workbook "Test of Master.xlxm" go to Sheet1 Cell "C15" and paste the clipboard

    4 Put the following Code in the Test of Master.xlsm


    Sub MyCopy()
    Dim Aa As Integer
    Dim Ab As Integer
    Dim Fname As String
    Dim Fname1 As String
    Aa = 1
    Ab = 15
    Worksheets.Item(1).Activate
    Cells(Ab, 3).Select
    Do While ActiveCell <> ""
    Workbooks("Test of Master.xlsm").Activate
    Worksheets.Item(1).Activate
    Cells(Ab, 3).Activate
    Fname = ActiveCell.Value
    Workbooks.Open (Fname)
    Fname1 = ActiveWorkbook.Name
    Worksheets.Item(1).Copy After:=Workbooks("Test of Master.xlsm").Worksheets.Item(Aa)
    Workbooks(Fname1).Activate
    Worksheets.Item(2).Copy After:=Workbooks("Test of Master2.xlsx").Worksheets.Item(Aa)
    Workbooks(Fname1).Activate
    Worksheets.Item(3).Copy After:=Workbooks("Test of Master3.xlsx").Worksheets.Item(Aa)
    Workbooks(Fname1).Close SaveChanges:=False
    Aa = Aa + 1
    Ab = Ab + 1
    Workbooks("Test of Master.xlsm").Activate
    Worksheets.Item(1).Activate
    Cells(Ab, 3).Activate
    Loop
    End Sub


    5 Run the Macro


    Good Luck TD

  3. #3
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post

    Hi Duthiet,

    Thanks for looking into this thread. My requirement in bit different. I have attached the sample files for better understanding on it. Please let me know if things are still unclear.

    Regards,
    JD
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    What I provided should work.

    I ran the macro and got the same results as in your Output 1; Output 2; and Output 3
    Attached are MyData01.xlsx [data file one]
    MyData02.xlsx [data file two]

    Aslo attached are the blank output files which are Output One Output Two and Output Three - to use the macro these files must be opened and their names must be Output One.xlsm; Output Two.xlsx; Output Three.xlsx.

    In Output One.xlsm you must entet the full pathname of each file you want opened and copied to the Output Workbooks. [One Two Three]

    In the sample I have set up only two workbooks; but you can put as many pathnames as you want, but they all must be in column C and there can be no blank rows between the names.

    Once you have put the full filepath of the exact location of the files on your computer in C15 and C16 AND you have open Output One; Output Two and Output Three run the macro.

    You should get the desired results in the desired sequence.

    Good luck
    TD
    Attached Files Attached Files

  5. The Following User Says Thank You to duthiet For This Useful Post:

    Jaggi (2015-06-02)

  6. #5
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Thanks for the macro it is working fine now!

    Regards,
    JD

Tags for this Thread

Posting Permissions

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