Hi Graham
I like your code, by the way. Very elegant.
I would just record a macro to see the syntax of Paste Special as values only. Since you have not cleared the clipboard you can leave your code as is, and just paste special as values only in a next line. That should do the trick.
Regards
Paul
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: Tuesday, 18 November 2014 5:42 AM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Help with Syntax for copying visible data as value only
Hello Everyone,
The following code works OK for me but I would prefer the data to be copied over as value only. Could anyone start me off with an idea of how to do this please? Thanks in anticipation, Graham
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")
End Sub
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 (2) |
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