excel vba for open, copy multiple workbooks then paste all in a new workbook sheet1

[jhae]

Hi can somebody please help me create macro for I want to open multiple workbooks (all are save in the same folder), copy all data from sheet1 of each workbook and paste it in a new workbook. On the new workbook sheet1, data of workbook1 will be paste then on its last row follows data of workbook2 and so on. After copying all data all workbook must be closed except the new workbook and the workbook that contains the macro.

 

[Myproblem]

I do not know about "Application.AutomationSecurity lines commented out. Still testing with them", i guess it is for security lines - it do not matter to me so much (or I do not know effects of it)

this seems to work great.
One more question: it was hard to me to figure out what range is used to copy from sheets (all data range from sheets). is it possible to modify this macro to say copy specific range like, range to be copy A1:D50?
txh

 

[pbornemeier]

Change this:
Worksheets(lX).Rows("1:" & lLastDataRow).Copy Destination:=ThisWorkbook.Sheets("Sheet1").Cells(lNextWriteRow, 1)
to this:
Worksheets(lX).Range("A1:D50").Copy Destination:=ThisWorkbook.Sheets("Sheet1").Cells(lNextWriteRow, 1)

 

[Myproblem]

thx you very much. ithis is great stuff

 

[jevi]

Hi Phil,

I have kind of the same problem, but i guess needs some changes...while i was reading through this thread just for information, thought let's try. I am really really basic at VBA, so just by reading here I have understood smth.

Let's see if I can explan better,

I have a folder named: November, and I have 18 workbooks inside with different names but the first sheet of 18 workbooks is named the same November, and 18 wb at B8 have the code of the units and go down to B54, most of those 18 files have the same format, structure. Now I do a summary of the information for each unit lets say Unit B8 to Unit B54 and i have a workbook, named each sheet with the code of the unit, and to do a summary of this unit with all the information that is in the 18 wb, and to make my job easier ( i found it by case myself), i open all the 18 workbooks, and at the first unit B8 (New workbook-sheet named B8 as the unit), I start entering the info in a manul way, let say =workbook1, range b, etc so for 18 workbooks. I takes you 5 minutes and then I copy this sheet in 54 sheets and to have this summary information for all the units what I do is CTRL + F and i replace only a number. As I said in 18 workbooks the units starts with B8, for the next one B9, i dont need to enter manually but say CTRL + F replace 8 with 9, and so on till the last one 8 with 54...this is really a cool way that I did found and I finish the summary for 54 units in 10-15 minutes. IT IS IMPORTANT ALWAYS TO KEEP YOUR WORKBOOKS WHERE YOU GET INFORMATION OPENED. And they should be the same cell in all the workbookds, so B8 to B54 at all the 18 workbooks.

Ok a big message but hope this might helps others also, if they have to do smth like this, and this method is very easy for me, but just wondering if I can do it with a macro smth that you wrote above.

Thank you a lot,
Jevila

 

[devenus]

Thanks Phil, Reaaly Supperb.
i have 2 requests,
1. when i ran this macro, it is copying from Heading, can we exclude heading
2. also can this be done using Master data work book instead of opening new workbook to pase the data.

Thanks
Devenus

 

[pbornemeier]

So you want to leave this code in a separate workbook and have its output go into Sheet1 of 'Master Data.xlsm'?
Do you want the Summary sheet to go into 'Master Data.xlsm' also?
Would 'Master Data.xlsm' be in the same directory as the source files?

 

[robmaag]

Dear Phil,

I have a series of excel workbooks in a folder and I want to copy these workbooks into a series of different workooks. How do I modify your code to do that?

The series of excel workbooks are in an array called 'wbA' and the series of excel workbooks wbA will copied into are 'wbB'...Your thoughts...

 

[pbornemeier]

Your end goal is not clear. If you have 10 workbooks in folder A what parts of which of them is to be copied into what parts of which of those in folder B?
Please post the code that you have so far (using CODE tags please) and provide a more detailed statement of what you want to do.

 

[robmaag]

Phil, Sorry for not being clear of my code. What I would like to do is to copy a particular excel spreadsheet and place each one of the excel spreadsheets into particular excel workbooks. There is an order by which I would like this to be completed...

Here is the scemario...

a. In folder c:\Reports
b. Copy excel each excel file

These are the sample names of the workbooks

"ALLIED PACIFIC OF CALIF"
"ALPHA MSO"
"AMM"
"ANCHOR MEDICAL"

Since these are not the exact name of the excel files, I did the following:

myArr(1) = Workbooks("ALLIED PACIFIC OF CALIF*").Name
myArr(2) = Workbooks("ALPHA MSO*").Name
myArr(3) = Workbooks("AMM*").Name
myArr(4) = Workbooks("ANCHOR MEDICAL*").Name

Then I want to copy the above excel files into the following type of excel workbooks

"Report-ALLIED PACIFIC OF CALIF"
"Report-ALPHA MSO"
"Report-AMM"
"Report-ANCHOR MEDICAL"

I did the following:

myArr1(51) = Workbooks("Report-ALLIED PACIFIC OF CALIF*").Name
myArr1(52) = Workbooks("Report-ALPHA MSO*").Name
myArr1(53) = Workbooks("Report-AMM*").Name
myArr1(54) = Workbooks("Report-ANCHOR MEDICAL*").Name

It would be nice if the copied excel files are placed as the first tab in the new excel workbook labeled 'Report'...

In other words, I would like to copy a series of excel workbooks into a series of different excel workbooks as tabs...

I hope that is clear...

Robert

-----------

Here is the code that I have developed so far....

Sub testStrArr()
Dim myArr(1 To 50) As String
Dim myArr1(51 To 100) As String
'Dim myStr$, ws As Worksheet
'Dim x As Integer
'Dim wbA As Workbook, wbB As Workbook
'Dir = "c:\Reports"
Dim FSO, fldr, fl
Dim wbA As Workbook, wbB As Workbook
Dim sh As Worksheet
Dim shCopAfter As Worksheet
'Dim MyPath As String

Set FSO = CreateObject("scripting.filesystemobject")
Set fldr = FSO.GetFolder("C:\Report")
On Error Resume Next

myArr(1) = Workbooks("ALLIED PACIFIC OF CALIF*").Name
myArr(2) = Workbooks("ALPHA MSO*").Name
myArr(3) = Workbooks("AMM*").Name
myArr(4) = Workbooks("ANCHOR MEDICAL*").Name
myArr(5) = Workbooks("APPLECARE*").Name
myArr(6) = Workbooks("AXMINSTER MEDICAL GROUP*").Name
myArr(7) = Workbooks("BROWN AND TOLAND*").Name
myArr(8) = Workbooks("CALIFORNIA PCP ON PPO*").Name
myArr(9) = Workbooks("CHOICE PHYSICIANS NETWK*").Name
myArr(10) = Workbooks("CITRUS VALLEY PHYSICIANS GROUP*").Name
myArr(11) = Workbooks("COAST*").Name
myArr(12) = Workbooks("DIGNITY HEALTH*").Name
myArr(13) = Workbooks("EMPIRE PHYSICIANS MEDGRP*").Name
myArr(14) = Workbooks("EPIC HEALTH PLAN*").Name
myArr(15) = Workbooks("FACEY*").Name
myArr(16) = Workbooks("HEALTHCARE PARTNERS*").Name
myArr(17) = Workbooks("HEMET COMMUNITY*").Name
myArr(18) = Workbooks("HILL PHYSICIANS MEDICAL GROUP*").Name
myArr(19) = Workbooks("HISPANIC PHYSICIAN*").Name
myArr(20) = Workbooks("JOHN MUIR*").Name
myArr(21) = Workbooks("KEY MEDICAL*").Name
myArr(22) = Workbooks("MAVERICK*").Name
myArr(23) = Workbooks("MCKESSON*").Name
myArr(24) = Workbooks("MCKESSON - VALLEY*").Name
myArr(25) = Workbooks("MCKESSON - VALLEY CARE SELECT*").Name
myArr(26) = Workbooks("MEMORIALCARE*").Name
myArr(27) = Workbooks("MID COUNTY*").Name
myArr(28) = Workbooks("MONARCH*").Name
myArr(29) = Workbooks("NAMM*").Name
myArr(30) = Workbooks("OMNI IPA*").Name
myArr(31) = Workbooks("PALO ALTO MEDICAL FOUNDATION*").Name
myArr(32) = Workbooks("PDTRUST*").Name
myArr(33) = Workbooks("SANJOSE*").Name
myArr(34) = Workbooks("SANTA CRUZ*").Name
myArr(35) = Workbooks("PREFERRED IPA OF CA*").Name
myArr(36) = Workbooks("PROSPECT*").Name
myArr(37) = Workbooks("RIVERSIDE MEDICAL CLINIC*").Name
myArr(38) = Workbooks("RIVERSIDE PHYSICIAN NETWORK*").Name
myArr(39) = Workbooks("SAN BERNARDINO MEDICAL GROUP*").Name
myArr(40) = Workbooks("SANDIEGO PHYSICIANS MED GRP*").Name
myArr(41) = Workbooks("SANTA CLARA COUNTY IPA*").Name
myArr(42) = Workbooks("SANTE COMMUNITY PHYS*").Name
myArr(43) = Workbooks("SGMF - SAN JOAQUIN*").Name
myArr(44) = Workbooks("SUTTER EAST BAY*").Name
myArr(45) = Workbooks("SYNERMED*").Name
myArr(46) = Workbooks("TORRANCE HOSPITAL IPA*").Name
myArr(47) = Workbooks("UCSD MG*").Name
myArr(48) = Workbooks("UNIVERSITY HEALTHCARE*").Name
myArr(49) = Workbooks("VANTAGE*").Name
myArr(50) = Workbooks("ALLCARE IPA*").Name
myArr1(51) = Workbooks("Report-ALLIED PACIFIC OF CALIF*").Name
myArr1(52) = Workbooks("Report-ALPHA MSO*").Name
myArr1(53) = Workbooks("Report-AMM*").Name
myArr1(54) = Workbooks("Report-ANCHOR MEDICAL*").Name
myArr1(55) = Workbooks("Report-APPLECARE*").Name
myArr1(56) = Workbooks("Report-AXMINSTER MEDICAL GROUP*").Name
myArr1(57) = Workbooks("Report-BROWN AND TOLAND*").Name
myArr1(58) = Workbooks("Report-CALIFORNIA PCP ON PPO*").Name
myArr1(59) = Workbooks("Report-CHOICE PHYSICIANS NETWK*").Name
myArr1(60) = Workbooks("Report-CITRUS VALLEY PHYSICIANS GROUP*").Name
myArr1(61) = Workbooks("Report-COAST*").Name
myArr1(62) = Workbooks("Report-DIGNITY HEALTH*").Name
myArr1(63) = Workbooks("Report-EMPIRE PHYSICIANS MEDGRP*").Name
myArr1(64) = Workbooks("Report-EPIC HEALTH PLAN*").Name
myArr1(65) = Workbooks("Report-FACEY*").Name
myArr1(66) = Workbooks("Report-HEALTHCARE PARTNERS*").Name
myArr1(67) = Workbooks("Report-HEMET COMMUNITY*").Name
myArr1(68) = Workbooks("Report-HILL PHYSICIANS MEDICAL GROUP*").Name
myArr1(69) = Workbooks("Report-HISPANIC PHYSICIAN*").Name
myArr1(70) = Workbooks("Report-JOHN MUIR*").Name
myArr1(71) = Workbooks("Report-KEY MEDICAL*").Name
myArr1(72) = Workbooks("Report-MAVERICK*").Name
myArr1(73) = Workbooks("Report-MCKESSON*").Name
myArr1(74) = Workbooks("Report-MCKESSON - VALLEY*").Name
myArr1(75) = Workbooks("Report-MCKESSON - VALLEY CARE SELECT*").Name
myArr1(76) = Workbooks("Report-MEMORIALCARE*").Name
myArr1(77) = Workbooks("Report-MID COUNTY*").Name
myArr1(78) = Workbooks("Report-MONARCH*").Name
myArr1(79) = Workbooks("Report-NAMM*").Name
myArr1(80) = Workbooks("Report-OMNI IPA*").Name
myArr1(81) = Workbooks("Report-PALO ALTO MEDICAL FOUNDATION*").Name
myArr1(82) = Workbooks("Report-PDTRUST*").Name
myArr1(83) = Workbooks("Report-SANJOSE*").Name
myArr1(84) = Workbooks("Report-SANTA CRUZ*").Name
myArr1(85) = Workbooks("Report-PREFERRED IPA OF CA*").Name
myArr1(86) = Workbooks("Report-PROSPECT*").Name
myArr1(87) = Workbooks("Report-RIVERSIDE MEDICAL CLINIC*").Name
myArr1(88) = Workbooks("Report-RIVERSIDE PHYSICIAN NETWORK*").Name
myArr1(89) = Workbooks("Report-SAN BERNARDINO MEDICAL GROUP*").Name
myArr1(90) = Workbooks("Report-SANDIEGO PHYSICIANS MED GRP*").Name
myArr1(91) = Workbooks("Report-SANTA CLARA COUNTY IPA*").Name
myArr1(92) = Workbooks("Report-SANTE COMMUNITY PHYS*").Name
myArr1(93) = Workbooks("Report-SGMF - SAN JOAQUIN*").Name
myArr1(94) = Workbooks("Report-SUTTER EAST BAY*").Name
myArr1(95) = Workbooks("Report-SYNERMED*").Name
myArr1(96) = Workbooks("Report-TORRANCE HOSPITAL IPA*").Name
myArr1(97) = Workbooks("Report-UCSD MG*").Name
myArr1(98) = Workbooks("Report-UNIVERSITY HEALTHCARE*").Name
myArr1(99) = Workbooks("Report-VANTAGE*").Name
myArr1(100) = Workbooks("Report-ALLCARE IPA*").Name

Set wbA = Array("myArr(1)", "myArr(2)", "myArr(3)", "myArr(4)", "myArr(5)", "myArr(6)", "myArr(7)", "myArr(8)", "myArr(9)", "myArr(10)", "myArr(11)", "myArr(12)" _
, "myArr(13)", "myArr(14)", "myArr(15)", "myArr(16)", "myArr(17)", "myArr(18)", "myArr(19)", "myArr(20)", "myArr(21)" _
, "myArr(22)", "myArr(23)", "myArr(24)", "myArr(25)", "myArr(26)", "myArr(27)", "myArr(28)", "myArr(29)", "myArr(30)" _
, "myArr(31)", "myArr(32)", "myArr(33)", "myArr(34)", "myArr(35)", "myArr(36)", "myArr(37)", "myArr(38)", "myArr(39)" _
, "myArr(40)", "myArr(41)", "myArr(42)", "myArr(43)", "myArr(44)", "myArr(45)", "myArr(46)", "myArr(47)", "myArr(48)" _
, "myArr(49)", "myArr(99)")

Set wbB = Array("myArr(50)", "myArr(51)", "myArr(52)", "myArr(53)", "myArr(54)", "myArr(55)", "myArr(56)", "myArr(57)", "myArr(58)", "myArr(59)", "myArr(60)", "myArr(61)" _
, "myArr(62)", "myArr(63)", "myArr(64)", "myArr(65)", "myArr(66)", "myArr(67)", "myArr(68)", "myArr(69)", "myArr(70)" _
, "myArr(71)", "myArr(72)", "myArr(73)", "myArr(74)", "myArr(75)", "myArr(76)", "myArr(77)", "myArr(78)", "myArr(79)" _
, "myArr(80)", "myArr(81)", "myArr(82)", "myArr(83)", "myArr(84)", "myArr(85)", "myArr(86)", "myArr(87)", "myArr(88)" _
, "myArr(89)", "myArr(90)", "myArr(91)", "myArr(92)", "myArr(93)", "myArr(94)", "myArr(95)", "myArr(96)", "myArr(97)" _
, "myArr(98)", "myArr(100)")
'For x = 1 To wbA.Workbooks.Count
' wbA.Workbooks(x).Copy After:=wbB.Workbooks((2 * x) - 1)
'Next x
'For Each fl In fldr
' Set pointer to first sheet in wbB Array
Set shCopAfter = wbB.Sheets(1)
' loop through the workbooks in wbA Array
For Each sh In wbA.Workbooks
' Copy sheet to wbB Array
sh.Copy Before:=shCopAfter
' If last sheet in book then set shCopyAfter to last sheet
If ActiveSheet.Index >= wbB.Workbooks.Sheets.Count Then
Set shCopAfter = ActiveSheet
Else
' Else set shCopyAfter to the one after the one just copied
Set shCopAfter = wbB.Workbooks.Sheets(ActiveSheet.Index + 1)
End If
Next
'Next fl
MsgBox "All Filter Excel Files are copied to the Excel Slicer Files are Complete. "
End Sub

 

[pbornemeier]

Workbooks and worksheets names cannot contain an asterisk.

Is this a correct restatement of what you want to do (but in a smaller scale):
All files of interest are in the same directory
All files are of the same type (.xls)

With
4 Source workbooks: "A", "B", "C", "D", each of which contains 3 worksheets
4 Target workbooks: "Report-A", "Report-B", "Report-C", "Report-D", which already exist and contain an arbitrary number (X) of worksheets

Copy each sheet in a Source workbook after the last sheet in the corresponding Target workbook
So that All of the sheets in workbook "A" will be copied into workbook "Report-A" with the first worksheet from Workbook "A" being placed after the last worksheet in "Report-A"
So that All of the sheets in workbook "B" will be copied into workbook "Report-B" with the first worksheet from Workbook "B" being placed after the last worksheet in "Report-B"
etc.