Looping a copy and paste macro from a dir. of files?!?!

Posted by John Rouse on July 02, 2001 9:13 AM

Title says it. I have a whole year of data seperated into different spreadsheets by days, all files are uniform, only the data is different. I need to take a group of cells from the day files and put them in order in a master sheet. I've written all I need except how to OPEN each subsequent file from the directory, then close it. A point and click macro would be to irratic. Currently I have to open each one, run the macro and close it. I have about 8 months left. I need to finish this macro, its a lot of data! Please help me, thanks a whole bunch.

Posted by mseyf on July 02, 2001 12:56 PM

someone could probably come up with something more elegant, but you could try:

Sub LoopSheets()
Dim strPath As String
Dim strCurrWorkbook As String
Dim aNameOfFile()
Dim intX As Integer
Dim intZ As Integer

intX = 0
strPath = "d:\" 'change to your path
Do
intX = intX + 1
ReDim Preserve aNameOfFile(intX)
If intX = 1 Then
aNameOfFile(intX) = Dir(strPath & "*.xls")
Else
aNameOfFile(intX) = Dir()
End If
Loop Until aNameOfFile(intX) = ""

For intZ = 1 To intX - 1

Workbooks.Open strPath & aNameOfFile(intZ)
'your macro here
Workbooks(aNameOfFile(intZ)).Close savechanges:=False
Next

End Sub

Hope this gets you started

Mark