Textfile-Macros Specific date

[Mick23456]

I need to import data from text file to excel and from that sheet(sheet1) need to fetch specific rows with title(row which have Transaction and successful and failed) to another sheet(sheet2) with color difference as Green for successful and Red from failed.

Help me with Macros VBA code

 

[sijpie]

Let me try to rephrase what you want to achieve.

1. You have a textfile (that comes in regularly)
2. You want to import the textfile into a sheet (sheet 1)
3. The data needs to be imported in columns
4. The first line contains a header 'Transaction'
5. If a line in the column 'Transaction' contains 'succesful' or 'failed' that row needs to be copied to sheet 2
6. In sheet 2 conditional formatting will colour cells green or red

 

[Mick23456]

Let me try to rephrase what you want to achieve.

1. You have a textfile (that comes in regularly)
2. You want to import the textfile into a sheet (sheet 1)
3. The data needs to be imported in columns
4. The first line contains a header 'Transaction'
5. If a line in the column 'Transaction' contains 'succesful' or 'failed' that row needs to be copied to sheet 2
6. In sheet 2 conditional formatting will colour cells green or red

Yes Correct

 

[sijpie]

Ok . Will do this soon

 

[sijpie]

When you import the textfile, I am assuming that Excel creates a new sheet to add the data to hold the data, with the data in a Table.

The macro then needs to filter the table on the Transaction column for 'failed' or 'successful'. Then the result needs to be copied to sheet2.

Try this macro after importing the data

Sub CopySuccessFail()

    Dim loTbl As ListObject
    Dim lC As Long
    
    With ActiveSheet
        Set loTbl = .ListObjects(1)
        
        lC = .Range("1:1").Find("Transaction").Column
        loTbl.Range.AutoFilter Field:=lC, Criteria1:="=failed", _
                    Operator:=xlOr, Criteria2:="=successful"
        loTbl.DataBodyRange.Copy
    End With
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A12").Select
End Sub

 

[Mick23456]

Error: Object Variable or With Block variable not set.

 

[sijpie]

On which line of code?

 

[Mick23456]

On which line of code?

Very first line

 

[sijpie]

After you import the data, does it appear as an Excel table? Recognisable as a table with striped rows and filterbuttons in the headers.

 

[sijpie]

Or is it purely unformatted text an numbers?

When I import from txt or csv files it appears as a formatted table. So the code assumes that.

If it is unformatted, then you could click un the table, on the start menu select format as table. Then test the macro.

Let me know either way, I can then modify the code