Hi Graham, I recorded this quickly. Please ignore the actual data, I just needed something to work with. My copy and paste is not as elegant as yours, but this is what I had in mind.
Sub Macro1()
ActiveSheet.Range("$A$1:$M$20").AutoFilter Field:=11, Criteria1:=Array("0", _
"0.2", "0.4", "0.8", "1.9", "100", "11.4", "11.8", "12.3", "15.5", "2.8", "4.7", "7.2", _
"9.3", "9.5"), Operator:=xlFilterValues
Selection.Copy
Sheets("NEW INVOICE").Select
Range("A7:L7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Alternatively you can try tgt.range instead of selection. tgt.Range("A7:L7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: Tuesday, 18 November 2014 11:22 PM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Re: Help with Syntax for copying visible data as value only
Thank you for your reply Paul. I have tried to find out by the "Record macro" method as shown below
Dim src As Worksheet
Dim tgt As Worksheet
Dim filterRange As Range
Dim copyRange As Range
Dim lastRow As Long
Set src = Sheets("JOBS")
Set tgt = Sheets("NEW INVOICE")
With src
AutoFilterMode = False
End With
lastRow = src.Range("A" & src.Rows.Count).End(xlUp).Row
Set filterRange = src.Range("A1:m" & lastRow)
Set copyRange = src.Range("B2:m" & lastRow)
filterRange.AutoFilter field:=1, Criteria1:=Sheets("JOBS").Range("K1").Value
copyRange.SpecialCells(xlCellTypeVisible).Copy tgt.Range("A7:L7")
tgt.Range("A7:L7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
but as shown I receive a Compile Error Sub or Function not defined and if I place Selection. before PasteSpecial I receive a RunTime Error 1004 PasteSpecial method of Range class failed. And that prompted me to try to get some help. Regards Graham
Posted by: Paul Vermeulen <paul.vermeulen@vulcantech.com.au>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar