Paste Special Help

[spalton]

Hi again,

I'm using the following code (from another site) to copy the contents of a worksheet, paste it into a new worksheet and save it. Problem is, it's maintaing the links back to the old workbook, if that is saved, as my users will definitely do I tell them not to, then the values in the saved file will change.

I need to somehow add a 'Paste Special - Values' command into the below somehow, any help greatly appreciated!

Private Sub CommandButton1_Click()
Dim bk As Workbook
Dim fName As Variant
ActiveSheet.Copy
' creates new workbook
Set bk = ActiveWorkbook
fName = Application.GetSaveAsFilename( _
FileFilter:="Excel Files (*.xls), *.xls", _
Title:="Specify Location for Copy:")
If fName = False Then
' user has chosen cancel
' so delete the copy
bk.Close Savechanges:=False
Else
bk.SaveAs fileName:=fName
bk.Close Savechanges:=False
End If
End Sub

 

[jasonb75]

Think this should do it, please try it on a test copy of your original file in case it overwrites the formula in the wrong sheet!

I'm sure there's a better way of doing it but this should at least give you a "quick fix" if it works. Could be slow depending on the amount of data in the sheet.

Private Sub CommandButton1_Click()
Dim bk As Workbook
Dim fName As Variant
ActiveSheet.Copy
' creates new workbook
Set bk = ActiveWorkbook
fName = Application.GetSaveAsFilename( _
FileFilter:="Excel Files (*.xls), *.xls", _
Title:="Specify Location for Copy:")
If fName = False Then
' user has chosen cancel
' so delete the copy
bk.Close Savechanges:=False
Else
bk.SaveAs Filename:=fName
 Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
bk.Close Savechanges:=True
End If
End Sub

 

[spalton]

worked brilliantly, cheers mate!