CODEWITHSUNDEEP
vbaexcelexcel-2007
Pika2u
Pika2u

Reputation: 11

Why is my code not copying text correctly when an equation in involved? (Excel Macro/Visual Basic)

In Excel, I altered some code to automatically copy text of a pre-selected range into a .txt file when the Command Button is clicked. For some reason, whenever this code attempts to copy from cells that have an =IF(AND( __, __ ), __, __) statement in it, it will not take the text, however it works perfectly fine for text that is simply entered into the cell with no form of equation. Also, the first few cells in the column are plain text without any equations, so having the equation in the code itself will not be desirable. What can I do to my code so it will not alter the information copied from Excel cells containing an equation referencing other cells? Code is exactly as seen below. Also, I am working in Excel 2007. Thank you!

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = ActiveSheet.Range("A1:B25").Copy
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
wb.Worksheets(1).Paste
saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close

End Sub

Upvotes: 1

Views: 857

Answers (1)

Tim Williams
Tim Williams

Reputation: 166351

Private Sub CommandButton1_Click()

    Dim wb As Workbook
    Dim saveFile As String
    Dim WorkRng As Range

    Set WorkRng = ActiveSheet.Range("A1:B25") '<<no .Copy    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set wb = Application.Workbooks.Add

    'WorkRng.Copy wb.Worksheets(1).Range("A1") 

    'copy values only
    WorkRng.Copy
    wb.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues

    saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
    wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
    wb.Close

End Sub

Upvotes: 2

Related Questions