Alright.
if I am understanding this correctly- you want to perform this operation on each file in that folder with the name:
09_04_DD.DAY
where DD is the day, to have the specific highlighted cells copied to a proper row in the resulting file?
Alright, I'll see what I can whip up. It might be a bit slow, since I'm haven't worked a whole lot with Excel, but it'll beat doing yourself, which I imagine is the point
OK-
whipped this up.
Public Sub PerformCopyPaste()
'B9 D9 F9 G9 I9 J9 L9 M9 O9
'To
'B(row),C(Row) etc.
'change the following variables to change the "base" name for searches.
Dim BaseName As String, BaseFolder As String
Dim copyto As Worksheet
Dim FilesOpen() As String, Filecount As Long
Dim I As Long, CurrFile As String
'set to active sheet, the one your looking at.
Set copyto = Application.ActiveWorkbook.ActiveSheet
'you can change this for different worksheets in separate folders to match the proper folder name- IE, OCMMay09 would be next months, I imagine.
BaseFolder = "N:\wastewater\OCM flowmeter\OCMApril09\"
'BaseFolder = "D:\TestFolder\"
BaseName = "09_04_"
'Step one: loop through the proper files. we will iterate until we do not find a matching filename.
I = 1
Do
CurrFile = ""
'here, we need to make sure there is a leading digit of the number is less then ten.
CurrFile = BaseName
If I < 10 Then CurrFile = CurrFile & "0"
CurrFile = CurrFile & Trim(Str(I)) & ".DAY"
'Now, we add BaseFolder to Basename, and see if the resulting file path exists.
CurrFile = BaseFolder + CurrFile
'check it. we cheat and just use DIR$()....
If Dir$(CurrFile) <> "" Then
Debug.Print "found file, " & CurrFile
'if the Dir function returns a value, then the file exists. we will place it in the array to process afterward.
Filecount = Filecount + 1
ReDim Preserve FilesOpen(1 To Filecount)
FilesOpen(Filecount) = CurrFile
Else
Exit Do
End If
'add one to I, to look for next file.
I = I + 1
Loop
'now we process the filenames we found, present in the filesOpen() array created in the previous loop.
Dim usebook As Excel.Workbook, currsheet As Worksheet
For I = 1 To UBound(FilesOpen)
'Set usebook = Application.Workbooks.OpenText
Call Workbooks.OpenText(Filename:=FilesOpen(I), origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True)
Set usebook = Application.Workbooks(Application.Workbooks.Count)
'now that "usebook" is open, we will iterate on each sheet. there should only be one, but what the heck.
'For Each currsheet In usebook.Worksheets
Set currsheet = usebook.Worksheets(1)
'B9 D9 F9 G9 I9 J9 L9 M9 O9
'To
'B(row),C(Row) etc.
Dim B9, D9, F9, G9, I9, J9, L9, M9, O9
B9 = currsheet.Cells(9, 2)
D9 = currsheet.Cells(9, 4)
F9 = currsheet.Cells(9, 6)
G9 = currsheet.Cells(9, 7)
I9 = currsheet.Cells(9, 9)
J9 = currsheet.Cells(9, 10)
L9 = currsheet.Cells(9, 12)
M9 = currsheet.Cells(9, 13)
O9 = currsheet.Cells(9, 15)
'now, copy to active sheet.
'Row number "I", starting from B onwards.
copyto.Cells(I + 4, 2) = B9
copyto.Cells(I + 4, 3) = D9
copyto.Cells(I + 4, 4) = F9
copyto.Cells(I + 4, 5) = G9
copyto.Cells(I + 4, 6) = I9
copyto.Cells(I + 4, 7) = J9
copyto.Cells(I + 4, 8) = L9
copyto.Cells(I + 4, 9) = M9
copyto.Cells(I + 4, 10) = O9
' Next currsheet
usebook.Close
Next I
copyto.Visible = xlSheetVisible
End Sub
to use it, copy every thing between the first "sub" line and the "end Sub" into your macro. replace everything inside there as well.
(be sure to make a backup of what you have.)
it worked alright on my machine with some test data I rigged up, but please double-check this for me before you run it on any serious data
you can make adjustments to the rows if your keen enough by changing the lines:
B9 = currsheet.Cells(9, 2)
D9 = currsheet.Cells(9, 4)
F9 = currsheet.Cells(9, 6)
G9 = currsheet.Cells(9, 7)
I9 = currsheet.Cells(9, 9)
J9 = currsheet.Cells(9, 10)
L9 = currsheet.Cells(9, 12)
M9 = currsheet.Cells(9, 13)
O9 = currsheet.Cells(9, 15)
and those below it as appropriate. notice that you need to reference the cells here with numbers for the column rather then letters.
I hope this helps you.