Excel macro to write from multiple workbooks to a single

Question

I have 300 separate workbooks that are formatted the same and want to take certain fields in each and write to one workbook so I can import an infile to SAS and run reports. I know how to do a macro to simple copy and paste but you have to know the name of each book and have them open and I have 300 separate workbooks all named different because they are sent in from various hospitals.

example: my book is called simply macro 1 hospital book is johnhopkins, another is centralflorida and so on

a possible code is:

johnhopkins.worksheets("sheet1").range("a1:g2").copy workbooks("macro.xslx").worksheets("sheet1").range("a1").insert shift:x1down

the problem is I want to copy and paste certain cells, not all in the workbook I am given to my single workbook and I am not sure how to select the cells I want from what they are sending. They are actuall cells B5, b8, g13, i13, j13, k13, l13

and I want to copy them to a1 through g2 on my workbook.

then the other problem is the workbooks are all named differently, all 300 of them. Is there a macro to just copy the workbooks i have opened without putting in the actual name everytime?

Pellizon Pellizon · Accepted Answer · 2013-03-14T19:15:25

You could start with something like this:

For i = 1 To Sheets.Count

Sheets.Item(i).Select
Range("A1:D10").Select
Selection.Copy
Sheets("Result").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveSheet.Paste

Next i

You can loop trough your sheets without knowing the name, and paste the results in your "Result" sheet Now, you just have to copy the range that you described, and paste in another sheet

Have fun!

Excel macro to write from multiple workbooks to a single

1 Answers