Selasa, 18 November 2014

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

 

Hi Graham, I recorded this quickly. Please ignore the actual data, I just needed something to work with. My copy and paste is not as elegant as yours, but this is what I had in mind.

 

Sub Macro1()

 

    ActiveSheet.Range("$A$1:$M$20").AutoFilter Field:=11, Criteria1:=Array("0", _

        "0.2", "0.4", "0.8", "1.9", "100", "11.4", "11.8", "12.3", "15.5", "2.8", "4.7", "7.2", _

        "9.3", "9.5"), Operator:=xlFilterValues

    Selection.Copy

    Sheets("NEW INVOICE").Select

    Range("A7:L7").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Application.CutCopyMode = False

 

End Sub

 

Alternatively you can try tgt.range instead of selection. tgt.Range("A7:L7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

 

 

From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: Tuesday, 18 November 2014 11:22 PM
To: ExcelVBA@yahoogroups.com
Subject: [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")

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: 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 (4)
----------------------------------
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:

Poskan Komentar