How to copy data from different excel workbooks into a sheet

Hi, Am new to this forum and need a little help.

I am trying to make a master database from approx 700 excel workbooks with 2 sheets in them. I want to copy some rows and column from the first sheet of each of the workbook into a new workbook. I want the data to be copied one below the other.

I read through come of the threads but was not able to get it done.

I am using excel 2007 to do this. Your help would sail me through this situation.

Thanks Rohit.

Just some clarification: is it only one column you are copying, with several rows, one from each workbook?
And you want to compile it all into one workbook: into one very long column, into several columns, into several Worksheets?

Hi,

Sorry about the confusion. I have made an example excel of the workbook I want to copy from and how i want the data to be compiled onto a new workbook. Attached is the same and I hope it helps. ThanksExample workbook.xlsx

If all your first sheets are set up the same, then maybe:

Sub RohitSharma()

Dim lr As Long

Range("A8:G14").Copy Workbooks("New Workbook").Sheets("Sheet1").Range("D" & Rows.Count).End(3)(2)
Range("A5").Copy Workbooks("New Workbook").Sheets("Sheet1").Range("A" & Rows.Count).End(3)(2)
Range("F5").Copy Workbooks("New Workbook").Sheets("Sheet1").Range("B" & Rows.Count).End(3)(2)
Range("G18").Copy Workbooks("New Workbook").Sheets("Sheet1").Range("C" & Rows.Count).End(3)(2)

Workbooks("New Workbook").Activate

lr = Cells(Rows.Count, 4).End(xlUp).Row

Sheets("Sheet1").Range(Range("A" & Rows.Count).End(3)(2), Range("C" & lr)).Value = " "



End Sub

Sorry about being a pain but where do i add this code...????

Sorry for the late response. For 2003 Tools>Macro>Visual Basic Editor>Lefthand side choose the project>Insert Module (this will be a standard Module) and paste it there.

Sorry about being a pain but where do i add this code...????

Hi, Sorry about the delayed response...thanks for the help...i tried this code but it gives me a run-time error "subscript out of range" ..?? I hope we are on the same page as i want to copy data from approx 700 excel files onto one master file...!!

You would have to open and activate each workbook and then run the code.