Extract Data from identical worksheets to a desired location

[sai srinu]

Hi all,

I have about 10 years data base i.e., 3000+ identical excel files (97-2003) with 6 to 7 sheets each.

I have to prepare a master sheet which has 7 values (to be picked from different sheets) for each corresponding day. I am using below macro code and it is not working.

--------------------------

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
MyFile = Dir("D:\New folder")

Do While Len(MyFile) > 0
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If

Workbooks.Open (MyFile)
Range("A25:A26").Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("sheet1").Range(Cells(erow, 1), Cells(erow, 2))

MyFile = Dir

Loop

End Sub
-------------------------

Hope I am clear. Seeking help. It will be great of you.

Thanks in advance.

Sai Srinu

 

[Somendra Misra]

Hi Sai,

Welcome on the forum. As a new user you must have a look of below link

http://chandoo.org/forum/forums/new-users-please-start-here.14/

And to your problem, consider code on below page, just see if they can help you

http://msdn.microsoft.com/en-us/library/gg549168.aspx

Regards,