VBA: Export specific range of specific worksheet to text file

[rollnation2]

Hello,

I am trying to modify a macro which exports a dynamic range to a text file, the range B2:O* where * is the last used cell in column O.

The code below work well but....

1. It prints all cells whereas I want to print only Columns B:O
2. I want the VBA to name the txt file what is in cell T2 of the worksheet.

Worksheet name = "KTR ERP UPLOAD FY22"

*********************************************************
Sub CrText()


Dim c00 As Variant
Dim textFilePath As String
Dim lngCounter As Long
Dim FF As Integer


textFilePath = CStr(VBA.CurDir) & "\mTextFile.txt"


FF = VBA.FreeFile
c00 = Range("A1").CurrentRegion


Open textFilePath For Output As #FF
For lngCounter = LBound(c00, 1) To UBound(c00, 1)
Print #FF, Join(Application.Index(c00, lngCounter, 0), vbTab)
Next
Close #FF
Shell "C:\WINDOWS\notepad.exe " & textFilePath, vbNormalFocus
End Sub

 

[Joe4]

I might approach this in a different manner.
Create a new sheet (or file), and copy ONLY exactly what you want to appear in your text file to that sheet.
Then save that in the desired text file format that you want.

 

[rollnation2]

Bump.

Thank you, Joe. This is what I am currently doing. The template format is formatted the same each time so i am looking to just add the VBA and a button for other users to complete the action.

Does anyone have an idea on modifying the VBA above so the code macro exports the specific range to a text file, the range B2:O* where * is the last used cell in column O.

The code below work well but....

1. It prints all cells whereas I want to print only Columns B:O
2. I want the VBA to name the txt file what is in cell T2 of the worksheet.

Worksheet name = "KTR ERP UPLOAD FY22"