Hi all,
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]
Desired Format:
[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:
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 SubAny help would be greatly appreciated.