-1

I need to create multiple files from my spreadsheet. Spread sheet contain First Name, Last Name and some info in third cell. My question is how can i create file for each line where file would be named First Name + Last Name.

Thank you so much in advance!

VladimirB
  • 3
  • 1
  • 3

1 Answers1

0

If the first name is in column A, and the last name is in column B, you can use this:

Sub test2()
Dim i&, lastRow&
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lastRow
    Workbooks.Add
    ActiveWorkbook.SaveAs ("C:\Users\[user name]\Desktop\" & Cells(i, 1) & " " & Cells(i, 2) & ".xls")
    ActiveWorkbook.Close
Next i
End Sub

Change the file path and extension as needed. It will save with the name FirstName LastName.xls as I have it.

Edit per comments below:

To make a new text file, and fill with the contents in column C, use the below (thanks to @Ben)

Sub create_TXT()
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object

Dim i&, lastRow&
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lastRow
    Set oFile = fso.CreateTextFile("C:\Users\[user name]\Desktop\" & Cells(i, 1) & " " & Cells(i, 2) & ".txt")
    oFile.WriteLine Cells(i, 3).Value
    oFile.Close
Next i
Set fso = Nothing
Set oFile = Nothing
End Sub
Community
  • 1
  • 1
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • HI, Tnx for such a quick answer, could i ask you one more question? If i wanted to put information from cell C in to the file so it would be cell A and cell B would be name of the file, and cell C would be content of the file. Is that possible? Thank you so much! – VladimirB Oct 06 '15 at 16:40
  • @VladimirB - that's definitely possible, but it gets a little more technical. If there's info. in cell C, where do you want that to go in your new file? Cell A1? Cell C1? etc... – BruceWayne Oct 06 '15 at 16:54
  • Hi, sorry for not making clear with my question, is it possible that file is .doc or .txt so that content of cell C can go as content. I understand that is technical and difficult. Thank you so much for your help!! – VladimirB Oct 06 '15 at 17:00
  • @VladimirB - I have updated the post with the new code that would create a .txt file (just change to .doc if you want a word document). – BruceWayne Oct 06 '15 at 17:13
  • Thank you so much worked perfectly. You just saved me of 2-3 days of work :) – VladimirB Oct 06 '15 at 17:29
  • @VladimirB - glad to have helped! Do you mind marking as the answer? – BruceWayne Oct 06 '15 at 17:29