Senin, 17 November 2014

[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:
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
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