Selasa, 18 November 2014

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