Senin, 17 November 2014

RE: [ExcelVBA] Help with Syntax for copying visible data as value only


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.









From: []
Sent: Tuesday, 18 November 2014 5:42 AM
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 <>
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: to enter the ezine, then search the ARCHIVES for EXCEL VBA.

Visit our ExcelVBA group home page for more info and support files:

More free tutorials and resources available at:




Tidak ada komentar:

Posting Komentar