Macro to Paste Special
 

Thanks to a previous posting I have had the following created for me for
copying data to a text file from a spreadsheet I've got.

I need one more thing on this - which if Tom could help this would be really
appreciated, however if anyone else is able to help with this amendment I would
be grateful.

Instead of the macro copying the data over as it is (unfortunately the
spreadsheet uses formulas and therefore when this is copied over it doesn't
copy over some of the data properly) - I need it to copy over as a value.

Once again thanks, Regards - Al ( )

Current Macro:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' rng holds range defined by name "data"
Dim rng As Range
' rngName holds the name for the file
Dim rngName As Range
Dim wkbk As Workbook
With ThisWorkbook.Worksheets(1)
If IsEmpty(.Range("B7")) Then Exit Sub
Set rng = .Range(.Range("B7"), _
.Cells(Rows.Count, 1).End(xlDown))
Set rng = rng.Resize(, 8)
End With
Set rngName = rng.Parent.Range("E2")
Set wkbk = Workbooks.Add
rng.Copy wkbk.Worksheets(1).Range("A1")
' if file exists, overwrite without prompt
Application.DisplayAlerts = False
wkbk.SaveAs FileName:=ThisWorkbook.Path _
& "\" & rngName.Value & ".txt", FileFormat:=xlText
Application.DisplayAlerts = True
wkbk.Close SaveChanges:=False
End Sub