VBA to export one column data into new text file

[asad]

Hi Guys,

Is it possible to export one column ("AT") from each sheet except for Master and Info from an Excel file and save as .txt file for each tab with the tab name as the file name in same folder? The row numbers are different in each sheet. They vary form 3 rows to 350 rows.

Thanks
Asad

 

[DanteAmor]

Try this

Sub export_one_column()
    Dim sh As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each sh In Sheets
        Select Case LCase(sh.Name)
            Case LCase("[COLOR=#0000ff]Master[/COLOR]"), LCase("[COLOR=#0000ff]Info[/COLOR]")
            Case Else
                sh.Range("AT:AT").Copy
                Workbooks.Add
                ActiveSheet.Paste
                ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & sh.Name & ".txt", _
                    FileFormat:=xlTextMSDOS, CreateBackup:=False
                ActiveWorkbook.Close False
        End Select
    Next
    MsgBox "done"
End Sub

 

[asad]

Thanks a lot Dante Amor.
It worked like a charm

Thanks
Asad

 

[asad]

Sorry Dante Amor,

I was a bit too quick in saying it worked. The code did work, but the values in column AT are all formulas at the moment and the code exported all #REFs for each cell.

How can I fix this?

 

[DanteAmor]

Sorry Dante Amor,

I was a bit too quick in saying it worked. The code did work, but the values in column AT are all formulas at the moment and the code exported all #REFs for each cell.

How can I fix this?

Use this

Sub export_one_column()
    Dim sh As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each sh In Sheets
        Select Case LCase(sh.Name)
            Case LCase("Master"), LCase("Info")
            Case Else
                sh.Range("AT:AT").Copy
                Workbooks.Add
[COLOR=#0000ff]                ActiveSheet.Range("A1").PasteSpecial xlValues[/COLOR]
                ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & sh.Name & ".txt", _
                    FileFormat:=xlTextMSDOS, CreateBackup:=False
                ActiveWorkbook.Close False
        End Select
    Next
    MsgBox "done"
End Sub

 

[asad]

Brilliant.

Thanks a lot Dante Amor.

 

[DanteAmor]

Brilliant.

Thanks a lot Dante Amor.

I'm glad to help you. I appreciate your kind comments.

 

[Taimaishoo]

@DanteAmor

This is wonderful!

I realize this is an old post, but instead of exporting, let's say Column C, from every sheet, I just want to export Column C from the current sheet that I click the macro assigned button on and have it named as <name_of_excel_file>.txt?

I have a spreadsheet with with 5 sheets. Step 1, Step 2, Step 3, Step 4, Export
The Export sheet takes all the various data from formulas and entries from the previous sheets, manipulates and cleans them up in Column C
This column can be anywhere from a few rows, to a few thousand rows, all depending on the previous sheets. Excess rows, may be blank due to removing blank cells, but the formulas
to determine if the cells should be blank or contain information are still there. Would there be a way to not export the blank cells?
I have a button, Click this to export to text file, that I wish to export only Column C from Export sheet and name the file <name_of_the_excel_file>.txt