Copy filtered data (Values only)

[Steve]

The following code copies filtered data:

Set Rng = ActiveSheet.AutoFilter.Range
Rng.Copy Destination:=Worksheets("Sheet2").Range("A1")

but copies values and formulae. (e.g. .PasteSpecial xlPasteValues)

How do I modify to the copy only the values
Thanks!

 

[Don Guillett]

Set Rng = ActiveSheet.AutoFilter.Range
Rng.Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues

Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Jacob Skaria]

Try one of the below macros..

Sub Macro1()
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
rng.Copy
Worksheets("Sheet2").Range("A1").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End Sub

Sub Macro2()
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
rng.Copy Destination:=Worksheets("Sheet2").Range("A1")
Worksheets("Sheet2").UsedRange = Worksheets("Sheet2").UsedRange.Value
End Sub

 

[Steve]

Thanks Jacob
That is working now. I was hoping this would fix another issue I am having
but it didn't. Once I copy to sheet2 I want to save that sheet as a csv.
The code creates an archive copy (with timestamp suffix) and also a working
copy (without time stamp) that is uploaded via FTP. This is the code that I
am using:

Dim strWksheet As String
Dim strPath As String
Dim strFileName As String
Dim strTimeStamp As String


strWksheet = "sheet2"
strPath = "mypath"
strFileName = "NewFile"
strTimeStamp = Format(Now(), "yyyy-mm-dd_hhmm")


Sheets(strWksheet).Copy
ActiveWorkbook.SaveAs Filename:=strPath & strFileName & _
strTimeStamp & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'close the new worksheet
ActiveWindow.Close
Application.DisplayAlerts = False
Sheets(strWksheet).Copy
ActiveWorkbook.SaveAs Filename:=strPath & Left(strFileName, 10) _
& ".txt", FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Application.DisplayAlerts = True
'close the new worksheet

Application.ScreenUpdating = True


exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: Copy_Data_Worksheet - " & Now()
GoTo exit_Sub