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

Topic

New Reply

Hi Experts
I am facing issue with something similar to the following thread – http://windowssecrets.com/forums/showthread//147970-VBA-Code-to-Open-Workbook-and-copy-data-into-destination-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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thanks for the macro it is working fine now!

Regards,
JD

Reply | Quote