Integrate ( xlPasteValues ) function in the code

Let say there is a code that copy past data from sheet to txt file

Option Explicit


Sub ConvertToTabDelimited()
    Dim TempSht As Worksheet
    Dim rRng As Range
    Dim sFullPath As String, sName As String
    
    sName = Sheets("parameters").Range("C8") & Format(Sheets("data").Range("G8"), " dd mm yy hh mm") & ".txt"

  
    sFullPath = ThisWorkbook.Path & Application.PathSeparator & sName
    With Application
        .ScreenUpdating = False
        On Error GoTo err_quit
        With Sheet1
            Set rRng = .Range(.Cells(8, 2), .Cells(.Rows.Count, 7).End(xlUp))
        End With
        Set TempSht = Sheets.Add
        rRng.Copy TempSht.Range("A1")
        TempSht.Copy
        ActiveWorkbook.SaveAs Filename:=sFullPath, FileFormat:=xlText, CreateBackup:=False
        .DisplayAlerts = False
        TempSht.Delete
        ActiveWorkbook.Close True
        .DisplayAlerts = True

err_quit:
        .ScreenUpdating = True
    End With
End Sub

Open in new window


The code work find however the last row (G) was created by using formula from the row (A) therefore it doesn't copy past in txt file properly but copy only (#REF!). There is a function xlPasteValues that can resolve my problem. Please help me integrate this function into my code.

Here you can find example what Im talking about
http://stackoverflow.com/questions/23937262/excel-vba-copy-paste-values-only-xlpastevalues
txt-output.PNG
ABC-26-05-15-09-30.txt
For-forum.xlsx
unnamed.png
SunnyXAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can use the following code...

Sub ConvertToTabDelimited()
    Dim TempSht As Worksheet
    Dim rRng As Range
    Dim sFullPath As String, sName As String
    
    sName = Sheets("parameters").Range("C8") & Format(Sheets("data").Range("G8"), " dd mm yy hh mm") & ".txt"

  
    sFullPath = ThisWorkbook.Path & Application.PathSeparator & sName
    With Application
        .ScreenUpdating = False
        On Error GoTo err_quit
        With Sheet1
            Set rRng = .Range(.Cells(8, 2), .Cells(.Rows.Count, 7).End(xlUp))
        End With
        Set TempSht = Sheets.Add
        rRng.Copy
        TempSht.Range("A1").PasteSpecial xlPasteValues
        TempSht.Copy
        ActiveWorkbook.SaveAs Filename:=sFullPath, FileFormat:=xlText, CreateBackup:=False
        .DisplayAlerts = False
        TempSht.Delete
        ActiveWorkbook.Close True
        .DisplayAlerts = True

err_quit:
        .ScreenUpdating = True
    End With
End Sub

Open in new window


Saurabh...
0
tilsantCommented:
This should help:
Option Explicit

Sub ConvertToTabDelimited()
    Dim TempSht As Worksheet
    Dim rRng As Range
    Dim sFullPath As String, sName As String
    
    sName = Sheets("parameters").Range("C8") & Format(Sheets("data").Range("G8"), " dd mm yy hh mm") & ".txt"

  
    sFullPath = ThisWorkbook.Path & Application.PathSeparator & sName
    With Application
        .ScreenUpdating = False
        On Error GoTo err_quit
        With Sheet1
            Set rRng = .Range(.Cells(8, 2), .Cells(.Rows.Count, 7).End(xlUp))
        End With
        Set TempSht = Sheets.Add
        rRng.Copy
        TempSht.Range("A1").PasteSpecial (xlPasteValuesAndNumberFormats)
        TempSht.Copy
        ActiveWorkbook.SaveAs Filename:=sFullPath, FileFormat:=xlText, CreateBackup:=False
        .DisplayAlerts = False
        TempSht.Delete
        ActiveWorkbook.Close True
        .DisplayAlerts = True

err_quit:
        .ScreenUpdating = True
    End With
End Sub

Open in new window


Modification in row 19 and insertion of row 20.

- tilsant
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tilsantCommented:
*Didn't refresh before posting last comment.

@ Saurabh: Doing only xlpastevalues, converts the date values into decimal numbers.
0
Rob HensonFinance AnalystCommented:
When copying from Excel and pasting into notepad, it will always paste values anyway, ie contents of the cell or the result of the formula. I suggest the #REF results are because the formula is failing in Excel or in the VBA before the copy and paste.

When I did just the straight copy and paste manually from Excel to Notepad, the dates went over correctly, not just as serial numbers.

Thanks
Rob H
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.