Need vba codes to split row (cut and paste to new workbook) based on criteria

[BrandonFromSingapore]

Hello forum.
I am going to have 3 files in a folder. One file is the macro which I was hoping to get codes to run. The other file is the csv file which I need the macro to work on. The third file is a text file which is a source of reference.
In the CSV File, first sheet, Column D (starting from row 2 because row 1 is the header), there are names of students.
Example in cell D2, it is “13 - Thomas Rich” and in Cell D5, it is “11 - Michelle Newton” and in cell D4, it is “09 - Cindy Bell”
These are all names of students and it goes from cell D2 to the last row.

My source of reference is the text file (Sample name list.txt) in this folder. This file will indicate the word, “Female” after a commar on the name.
Example, “02 – Jennifer Crown,Female”
On the next line, it may show “86 – Tom Hardy” (the male names will not be indicated that this is a male name)
Only female name such as “98 – Mary Brown,Female” will be indicted in this text file.

Is there a way to code a VBA into the macro so that it will use the source file and identify the female names and extract the whole row (I only need from column A to Q) into a separate new excel file? I need a new excel file for the female names (with header) and another file for the male students
Thank you.

I hope I am not making anyone confused. Thank you so much!

[arlu1201]

Would you always receive the reference file as .txt?

[Leith Ross]

Hello BrandonFromSingapore,

The macro below has been added to the attached workbook. This will split the workbook names using the text file. The file path is set to the current directory. You can change this if you need to. The names are split into two different workbooks. Each workbook contains a single sheet. The header row from the main workbook is copied into the new workbooks. The user is responsible for saving and naming the new workbooks.

Please Login or Register  to view this content.

[BrandonFromSingapore]

Hello, the debugger prompt me to an error on this line

Open Filepath & Filename For Input Access Read As #txtFile

error message run time error 53
file not found

[BrandonFromSingapore]

Hello Arlu1201, Yes. the reference file is a text file with extension txt
This can however be converted to a csv file just by changing the extension to csv I believe and instead of opening it using the text editor, it will open in excel if I changed the extension to csv

[Leith Ross]

Hello BrandonFromSingapore,

You need to change the file path and file name in the macro code. These need to match the path and file name of the text file on your computer.

Please Login or Register  to view this content.

[BrandonFromSingapore]

Hello, I am sorry for asking as I am very new to this.

is this correct?


Filepath = "C:\Users\brandon\Downloads\temp folder\First testing"
Filename = "Reference File.txt"


I placed the macro file and the reference file into this folder called "first testing"

[Leith Ross]

Hello BrandonFromSingapore,

You need to add a back slash to the file path like below...

Please Login or Register  to view this content.

[BrandonFromSingapore]

Hello, I was intending to have these code on a personal macro instead of writing this into a module on the file to be split because I will need to split similar files like this every week and I hope to use a macro that will run as a "Tool"? Perhaps I was hit "Ctrl" and Shift and "B" and it will run on the file which I have opened.

Do you know how I can do this? Thank you

[BrandonFromSingapore]

Hello Leith, it seems to only get one row of each female into the file. Do you know if the code needs to loop? because i need to extract not just one female name once but every row because the name does different activity. The total rows in both the female file and the male file should be equal to the rows in the original file. I need them to be split fully.

[Leith Ross]

Hello BrandonFromSingapore,

I am not sure what the problem is. The macro runs correctly on the files you posted. Can you post your current workbook?