Exporting to Text File not working

[Mstg007]

I am trying to use the below macro code to export a column within a sheet to a txt file. The final output shows " #REF! " within the text file. Not sure what I did.

Sub EXPORT()


'Update 20200131
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
'Set WorkRng = Sheets("Sheet1").Range("B1:B500")
Set WorkRng = Sheets("Sheet1").Range("B1:B" & Cells(Rows.Count, "J").End(xlUp).Row)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).Paste
saveFile = Application.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt")
wb.SaveAs Filename:=saveFile, FileFormat:=xlTextPrinter, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub

Thanks for any help!

 

[juddaaaa]

Works Here.

Book1
	A	B	C	D
1		1
2		2
3		3
4		4
5		5
6
7
8
Sheet1

Try changing this line

Set WorkRng = Sheets("Sheet1").Range("B1:B" & Cells(Rows.Count, "J").End(xlUp).Row)

to this

Set WorkRng = Sheets("Sheet1").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)

 

[Joe4]

Are there errors in the data file you are exporting from?
It might be helpful if you can provide a sample of the file you are trying to export.

 

[Mstg007]

Thank you for checking this out. Here is the workbook. I still have no idea.

File.Town - Temporary File Upload

File.Town is a free temporary file upload service. We will only store your data as long as you like. Optionally password protected.

file.town

 

[Fluff]

Change

wb.Worksheets(1).Paste

to

wb.Worksheets(1).Range("A1").PasteSpecial xlPasteValues

This will paste the values only, rather than the formula.

 

[Mstg007]

I think you got it! Thank you so much.

wb.Worksheets(1).Range("A1").PasteSpecial xlPasteValues

I did not think that would be a difference.

 

[Fluff]

Because your formula was looking at 1 cell to the left, when you paste it to col A there is no 1 cell to the left hence you get the #REF error.