Prev: Insert Shape Based on Cell Changes
Next: Copy to Named Range
From: Steve on 29 Apr 2010 09:06
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!
From: Don Guillett on 29 Apr 2010 09:13
Set Rng = ActiveSheet.AutoFilter.Range
Rng.Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Steve" <Steve(a)discussions.microsoft.com> wrote in message
news:5E54D33F-2DE0-48E4-8D27-4F08CD203D66(a)microsoft.com...
> 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!

From: Jacob Skaria on 29 Apr 2010 09:28
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

--
Jacob (MVP - Excel)


"Steve" wrote:

> 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!
From: Steve on 29 Apr 2010 10:09
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
"Jacob Skaria" wrote:

> 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
>
> --
> Jacob (MVP - Excel)
>
>
> "Steve" wrote:
>
> > 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!
 | 
Pages: 1
Prev: Insert Shape Based on Cell Changes
Next: Copy to Named Range