Exporting to a txt file

[ajshap1]

I have multiple rows within a worksheet and need them to automatically save into a separate txt file.

Example:

I have a workbook with rows b-z. Instead of taking each of the rows and copying and pasting it into another separate excel file, then saving in a txt format, is there a way to automate this?

So that the following will occur:

Column B 950 line is one file
Column C 950 lines is another
column D lines in another txt file.....etc

I have some code listed below, however, it reads the first column cell by cell. It does not group column by column grouping all data within that column as on txt file

Thanks

Sub notebook_save() 
    Set wkbk = Workbooks.Add 
    Sheets("Sheet1").Select 
    RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row 
    For i = 1 To RowCount 
        Sheets("Sheet1").Select 
        Range("a" & i).Select 
        ActiveCell.EntireRow.Copy 
        Sheets("Sheet2").Select 
        Range("a1").Select 
        ActiveSheet.Paste 
        Application.DisplayAlerts = False 
        ActiveWorkbook.SaveAs _ 
        Filename:="c:\save" & i & ".txt", _ 
        FileFormat:=xlTextMSDOS 
        wkbk.Close 
    Next 
    Application.DisplayAlerts = True 
End Sub

[Ron Weller]

Try this version:

Sub notebook_save()
  On Error GoTo Er
  Dim I As Long
  Dim c As Long
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim wst As Worksheet
  
  Set ws = Sheets(1)
  c = ws.UsedRange.Columns.Count
  
  Set wb = Workbooks.Add
  Set wst = wb.Sheets.Add(, Worksheets(Worksheets.Count))
      
  
  For I = 1 To c
    ws.Activate
    ws.UsedRange.Columns(I).Copy
    wst.Paste
    Application.DisplayAlerts = False
    wst.SaveAs ActiveWorkbook.Path & "\save" & I & ".txt", xlTextMSDOS, , , , , False
    Application.DisplayAlerts = True
    wst.UsedRange.Clear
    
  Next I
  
Ex:
  Application.DisplayAlerts = False
  wb.Close False
  Application.DisplayAlerts = True
  ws.Activate
  Set wst = Nothing
  Set wb = Nothing
  Set ws = Nothing
  Exit Sub
Er:
  MsgBox Err.Description
  Resume Ex
  Resume
  
End Sub

[ajshap1]

Hi Ron,

On the txt output files I recived #REF! for each of the lines items. Why would this be happening?

Thanks

[Ron Weller]

I only tested this on a very simple test sheet, one without formulas in any of the cells. It has got to be the formulas. Try using the PasteSpecial:
wst.PasteSpecial xlPasteValuesAndNumberFormats

[ajshap1]

It worked! Thanks