Macro to export range from different worksheet (not active sheet) to txt file

  • Hi!

    Following Macro:

    1. Exports range from worksheet "Names" (sheet that may not be the currently active sheet) to txt file with comma separated values in text file.
    2. The export of range starts from Row 2 and consists of data till last row for columns F & G.

    However, I now need export data from column F to column AV viz. 43 columns, and modifying the macro to manually write all the columns in the macro would be time consuming (and probably slow down execution too).

    I was wondering if the macro can be modified to export data in all 43 columns with only changing a few lines of code.If not, perhaps someone could help me with fresh code for the above requirements.
    I would be OK with the data being separated with commas or with tabs, since I need to re-import the data to another excel file.

    [VBA]Sub ExportData()
    On Error Resume Next

    Dim FilePath As String
    Dim LastRow As Long
    Dim CellData As String
    Dim CellData1 As String
    Dim WS2 As Worksheet
    Dim i As Long
    Dim RowsNos As Long

    Set WS2 = Workbooks("Book1.xlsm").Sheets("Names")
    FilePath = "C:\Data" & "\" & "Data.txt"
    RowsNos = Workbooks("Book1.xlsm").Sheets("Names").Range("F" & Rows.Count).End(xlUp).Row
    Open FilePath For Output As #1
    For i = 2 To RowsNos
    CellData = WS2.Cells(i, 6).Value '<< Col F is being exported
    CellData1 = WS2.Cells(i, 7).Value '<< Col G is being exported
    Print #1, CellData & "," & CellData1
    Next i
    Close #1
    End Sub[/VBA]

    Note: Sample file attached.

  • Hi,

    Perhaps just try looping through all the columns, something along the following lines...

    Note: The above is off the top of my head so not tested.


    Tom Rowe...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!