Macros to export from single worksheet based on parameters to multiple txt files

[anuradhagrewal]

Hi
I have this excel file with serial numbers of employees. I need to make individual text files of 500 serial numbers.
The master list is enclosed.
What I am looking for is that I am able to run a VB script that automatically that
1)Opens the worksheet-->DATA( main sheet) of the workbook file.
2)Pick up the first 500(A501) serial numbers in DATA---> copies it and then creates a----> text file name "list1.txt".
3)Then the code picks up(in a loop) from the 501(A502) serial number-----> copies it and then creates a----> text file name "list2.txt" and so on...
There are 3350 serial numbers in (3351 rows) and hence it should make around 7 text files.

I am completely lost and have no clue how to do it.
Can anyone help me out with this ... it will be a lifesaver.

 

[JLGWhiz]

This seemed to work OK.

Sub t()
Dim sh As Worksheet, x As Long, fPath
fPath = ThisWorkbook.Path
Set sh = ActiveSheet
x = 1
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row Step 500
    Workbooks.Add
    sh.Cells(i, 1).Resize(500).Copy ActiveWorkbook.Sheets(1).Range("A2")
    ActiveWorkbook.SaveAs fPath & "\List" & x & ".txt", 42
    ActiveWorkbook.Close False
    x = x + 1
Next
End Sub

Couldn't include opening the file because no info provided on path and format of workbook.

 

[anuradhagrewal]

Thanks a ton ... it worked for me.
However I have been unable to figure out how to add another column
For eg the Serial number of an employee is in A1 and his/her name is in A2.
Now the list only enlists his/her serial number. Is it it possible in this code that for an employee serial number his/her name also appears in the txt file.
Please help me out with this trivial query. You have already been of great help.

 

[JLGWhiz]

Should I just make up the names or would you like to share with us where the names would be found?

 

[anuradhagrewal]

Should I just make up the names or would you like to share with us where the names would be found?

Hi
Just make up names.. or even better if the code allows name that are in the field to be picked up as I have close to 1500 or so employees.
Much appreciate your gesture and thanks a gazillion

 

[JLGWhiz]

I was being facetious about making up the names. To modify the code so that it shows the names in the new workbooks, I need to know where on the source sheet the names are located. For example, if the names are in column B then change this line of code

sh.Cells(i, 1).Resize(500).Copy ActiveWorkbook.Sheets(1).Range("A2")

To this

sh.Cells(i, 1).Resize(500, 2).Copy ActiveWorkbook.Sheets(1).Range("A2")

and it will then copy both columns A and B to the new workbook and save it as a text file.
But I cannot write code if you do not tell me where to find the data.

 

[anuradhagrewal]

The code worked.... thanks again for such a fast response...
On the lighter side buddy I would have shared the entire list of 1500 employees if my manager was cool with it. But hey WTH.... you have made my day.
Thanks Thanks Thanks

 

[JLGWhiz]

The code worked.... thanks again for such a fast response...
On the lighter side buddy I would have shared the entire list of 1500 employees if my manager was cool with it. But hey WTH.... you have made my day.
Thanks Thanks Thanks

You're welcome,
regards, JLG