My current state of macro opens my text files in excel(via file picker) and delimits them.
However now they are in a matrix format and would like to get them in a more legible format.
My current format:
[Blocked Image: http://i.imgur.com/7ckM5dF.jpg]
[Blocked Image: http://i.imgur.com/hmIRubX.jpg]
this format is continue down the page and repeats.
A1 is needed and stays the same for several matrices then changes.
A5:A7 is the same for each batch.
B5:B7 changes each batch. (first name)
L10:L24 change each batch, (last name)
Numbers in matrix will change for each person.
Values circled is actually what needs to be extracted.
My current code:
Sub Rec() Dim fileNames As Object, errCheck As Boolean Dim ws As Worksheet, wks As Worksheet, wksSummary As Worksheet Dim y As Range, intRow As Long, i As Integer ' Turn off screen updating and automatic calculation With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With 'get user input for files to search Set fileNames = CreateObject("Scripting.Dictionary") errCheck = UserInput.FileDialogDictionary(fileNames) If errCheck Then Exit Sub End If For Each Key In fileNames 'loop through the dictionary On Error Resume Next Workbooks.OpenText Filename:=fileNames(Key), _ DataType:=xlDelimited, _ Other:=True, _ OtherChar:=";" On Error GoTo 0 ' or your custom error handler 'paste into rec sheet? Next 'End of the fileNames loop Set fileNames = Nothing ' Reset system settings With Application .Calculation = xlCalculationManual .ScreenUpdating = True .Visible = True End With End Sub
Any help would be greatly appreciated.