Hi All,
Need some help - I have the below code to copy a range of cells to a text file; however, when it copies and creates the file the text file shows only #REF in each selection. I need this to copy the values of the formulas in the cells. Each cell has a VLOOKUP formula and data validation... I need the displayed values to export. Can you help?
Need some help - I have the below code to copy a range of cells to a text file; however, when it copies and creates the file the text file shows only #REF in each selection. I need this to copy the values of the formulas in the cells. Each cell has a VLOOKUP formula and data validation... I need the displayed values to export. Can you help?
Code:
Sub ExportRangetoFile()'Update 20130913
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
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:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub