[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: grahamfellows2002@yahoo.co.uk

---

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: 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

----------------------------------

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___